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

Question

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

到目前为止，我已经

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

启动作业的是哪一个，如果作业运行成功还是失败，最好的恢复方法是什么？

Ok做了一个编辑，并使用了WAITFOR延迟，因为作业通常在3-4分钟之间运行，不会超过4分钟。作业有没有更有效的方法呢？

Answer 1

您可以运行以下查询：

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

它将返回一个列run_status。状态包括：

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

有关MSDN的更多信息

编辑：您可能希望轮询您的作业并确保其已执行。您可以从sp_help_job procedure获取此信息。当此过程返回状态4时，表示作业处于空闲状态。然后就可以安全地检查它的运行状态了。

您可以使用以下代码进行轮询：

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，我们就知道作业已经完成，可以安全地检查作业历史记录了。

Answer 2

对于所有不允许使用OPENROWSET命令的人来说，这可能会有所帮助。我在这里找到了解决方案的起点：

http://social.msdn.microsoft.com/Forums/en-US/89659729-fea8-4df0-8057-79e0a437b658/dynamically-checking-job-status-with-tsql

这取决于这样一个事实，即在作业以某种方式完成之后，msdb.dbo.sysjobactivity表的某些列首先被填充。

-- Start job
DECLARE @job_name NVARCHAR(MAX) = 'JobName'
EXEC msdb.dbo.sp_start_job @job_name = @job_name


-- Wait for job to finish
DECLARE @job_history_id AS INT = NULL

WHILE @time_constraint = @ok
BEGIN
    SELECT TOP 1 @job_history_id = activity.job_history_id
    FROM msdb.dbo.sysjobs jobs
    INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
    WHERE jobs.name = @job_name
    ORDER BY activity.start_execution_date DESC

    IF @job_history_id IS NULL
    BEGIN
        WAITFOR DELAY '00:00:10'
        CONTINUE
    END
    ELSE
        BREAK
END


-- Check exit code
SELECT history.run_status
FROM msdb.dbo.sysjobhistory history
WHERE history.instance_id = @job_history_id

您可能需要检查WHILE-loop允许运行多长时间。我选择在示例中省略这一部分。

微软退出代码等指南：http://technet.microsoft.com/en-us/library/ms174997.aspx

Answer 3

这是一个脚本，它将检查作业的状态，并在作业尚未运行时运行它。

declare @xp_results table (
job_id                UNIQUEIDENTIFIER NOT NULL,
last_run_date         INT              NOT NULL,
last_run_time         INT              NOT NULL,
next_run_date         INT              NOT NULL,
next_run_time         INT              NOT NULL,
next_run_schedule_id  INT              NOT NULL,
requested_to_run      INT              NOT NULL, -- BOOL
request_source        INT              NOT NULL,
request_source_id     sysname          COLLATE database_default NULL,
running               INT              NOT NULL, -- BOOL
current_step          INT              NOT NULL,
current_retry_attempt INT              NOT NULL,
job_state             INT              NOT NULL)

DECLARE @job_id uniqueidentifier ;
select @job_id = job_id from msdb.dbo.sysjobs where name = 'Job1';
insert into @xp_results
EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id

select case when running = 1 then 'Currently Running' else '' end as running, 
case job_state 
    when 0 then 'Not Idle or Suspended'
    when 1 then 'Executing Job'
    when 2 then 'Waiting For Thread'
    when 3 then 'Between Retries'
    when 4 then 'Idle'
    when 5 then 'Suspended'
    when 6 then 'WaitingForStepToFinish'
    when 7 then 'PerformingCompletionActions'
end as job_state
from @xp_results

IF (select running from @xp_results) <> 1
    EXEC msdb.dbo.sp_start_job 'Job1'