從 SSRS 執行作業時返回 SSRS 中的 SQL 代理作業狀態

我正在從 SSRS 呼叫 SQL 代理作業並使用參數來確定要執行的作業。

我可以很好地執行這項工作。我要做的是根據工作狀態向使用者返回一條消息。

以下是它的執行方式：

IF @Job = 'Run'

BEGIN

EXEC msdb.dbo.sp_start_job 'Run selected job'

END

IF @Job = 'Delete Previous Run' AND @Confirm = 'DELETE'

BEGIN 

EXEC msdb.dbo.sp_start_job 'Delete Previous Run'

END

當我PRINT在語句之後放入程式碼EXEC時，唯一返回的是Job '<job name>' completed successfully

我希望在作業完成後立即返回 SQL 代理狀態。我怎樣才能做到這一點？

該sp_start_job過程啟動作業，然後在作業非同步執行時返回給您。它不會等待作業執行——只需啟動它並離開。

如果你想讓你的 SQL 坐下來等待作業執行，你需要在你的 T-SQL 中引入一個循環WAITFOR DELAY 'HH:MI:SS'，然後檢查作業狀態，然後重複直到作業達到可以返回的終端狀態給使用者。

sp_help_job將為您提供作業狀態，但它使用#temp表，如果您嘗試自己將結果sp_help_job插入臨時表，這可能會導致程式碼出現問題，您將遇到錯誤 8164，“無法嵌套 INSERT EXEC 語句。” 有些人會使用xp_sqlagent_enum_jobs一個未記錄的內部過程，它會返回類似的結果。但是，因為它沒有文件化，我不希望在生產程式碼中使用它，因為行為可能會在 SQL Server 的未來版本或 CU 中發生變化。

相反，我更喜歡直接在 msdb 中查詢表以確定作業狀態。

每次圍繞該循環，您都會執行以下操作來獲取作業狀態（或從我的開源 DBA 數據庫中獲取函式，我將在我的部落格文章中進一步描述）：

DECLARE @JobName nvarchar(200) = N'';

SELECT TOP 1
   JobName        = j.name,
   IsRunning      = CASE
                      WHEN ja.job_id IS NOT NULL
                          AND ja.stop_execution_date IS NULL
                        THEN 1 ELSE 0 
                      END,
   JobLastOutcome = CASE
                      WHEN ja.job_id IS NOT NULL
                          AND ja.stop_execution_date IS NULL THEN 'Running'
                      WHEN jh.run_status = 0 THEN 'Failed'
                      WHEN jh.run_status = 1 THEN 'Succeeded'
                      WHEN jh.run_status = 2 THEN 'Retry'
                      WHEN jh.run_status = 3 THEN 'Cancelled'
                    END
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobactivity ja 
   ON ja.job_id = j.job_id
      AND ja.run_requested_date IS NOT NULL
      AND ja.start_execution_date IS NOT NULL
LEFT JOIN msdb.dbo.sysjobsteps js
   ON js.job_id = ja.job_id
      AND js.step_id = ja.last_executed_step_id
LEFT JOIN msdb.dbo.sysjobhistory jh
   ON jh.job_id = j.job_id
      AND jh.instance_id = ja.job_history_id
WHERE j.name = @JobName
ORDER BY ja.start_execution_date DESC;

引用自：https://dba.stackexchange.com/questions/308072