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