从存储过程执行 SQL Server 代理作业并返回作业结果

2021-12-13 00:00:00 sql tsql sql-server-2008 sql-server jobs

需要有一个存储过程来调用 SQL Server 代理作业并返回该作业是否成功运行.

Need to have a stored procedure that calls a SQL Server Agent Job and returns whether or not the job ran successfully or not.

到目前为止我有

So far I have

CREATE PROCEDURE MonthlyData
AS
EXEC msdb.dbo.sp_start_job N'MonthlyData'

WAITFOR DELAY '000:04:00'

EXEC msdb.dbo.sp_help_jobhistory @job_name = 'MonthlyData'
GO

哪个开始工作，如果工作成功与否，返回的最佳方法是什么?

Which starts the job, whats the best way to get back if the job ran successfully or not?

Ok 进行了编辑并使用了 WAITFOR DELAY，因为该作业通常在 3-4 分钟之间运行，从不超过 4 分钟.该作业是否有更有效的方法来完成?

Ok made an edit and used WAITFOR DELAY as the job normally runs between 3-4 mins never longer than 4. Does the job but is there a more efficient way to do it?

推荐答案

您可以运行查询:

EXEC msdb.dbo.sp_help_jobhistory 
    @job_name = N'MonthlyData'

它将返回一列 run_status.状态为:

It'll return a column run_status. Statuses are:

 0 - Failed
 1 - Succeeded
 2 - Retry
 3 - Canceled

有关 MSDN

编辑:您可能想要轮询您的工作并确保它已执行.您可以从 sp_help_job 过程中获取此信息.当此过程返回4 状态时，表示作业空闲.然后检查它的运行状态是安全的.

EDIT: You might want to to poll your job and make sure it's executed. You can get this information from sp_help_job procedure. When this procedure returns status of 4 it means the job is idle. Then it's safe to check for it's run status.

您可以使用以下代码进行投票:

You can poll using following code:

DECLARE @job_status INT
SELECT @job_status = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;','exec msdb.dbo.sp_help_job @job_name = ''NightlyBackups''')

WHILE @job_status <> 4
BEGIN
    WAITFOR DELAY '00:00:03'
    SELECT @job_status = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;','exec msdb.dbo.sp_help_job @job_name = ''NightlyBackups''')
END

EXEC msdb.dbo.sp_help_jobhistory 
    @job_name = N'NightlyBackups' ;
GO

此代码将检查状态，等待 3 秒钟，然后重试.一旦状态为 4，我们就知道工作已完成，可以安全地检查工作历史记录.

This code will check for the status, wait for 3 seconds and try again. Once we get status of 4 we know the job is done and it's safe to check for the job history.

相关文章