Executing SQL Server Agent Job from a stored procedure and returning job result

Question

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 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?

Answer 1

For all you guys who are not allowed to use the OPENROWSET command, this might help. I found the start for my solution here:

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

This relies on the fact that some columns of the msdb.dbo.sysjobactivity table first get populated after the job finishes in one way or the other.

-- 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 

You might want to put in some checks for how long the WHILE-loop is allowed to run. I chose to keep that part out of the example.

Microsoft guidance for exit codes etc.: http://technet.microsoft.com/en-us/library/ms174997.aspx

Answer 2

You can run the query:

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

It'll return a column run_status. Statuses are:

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

More info on MSDN

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 

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.