从存储过程中执行SQL Server代理作业并返回作业结果 [英] Executing SQL Server Agent Job from a stored procedure and returning job result

问题描述

需要具有一个存储过程，该存储过程调用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.

这篇关于从存储过程中执行SQL Server代理作业并返回作业结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！