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

<rant> I think it's ridiculous that SQL Server doesn't provide any hard id or reference to the specific instance of the job you executed. So here we are hoping that if we filter the job step history based on when we executed it that it will pick the right job instance, praying that that we avoid a data race by magic. Actually insane for a billion dollar data product. </rant>

ANYWAY, this is a stored procedure that executes a job by name, using sysjobactivity+sysjobhistory to wait up to a specified number of seconds for it to complete.

CREATE procedure [dbo].[StartAgentJobAndWait] @jobName nvarchar(128), @maxWaitSeconds int = 360
/*
Raises an exception if Job @jobName does not exist.
Returns int:
    -2 = job did not complete before @maxWaitSeconds
    -1 = failed to start
     0 = Failed
     1 = Succeeded
     2 = Retry
     3 = Canceled
     4 = In Progress 
*/
AS BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @start     DATETIME         = DATETRUNC(second,GETDATE())
    ,       @jobId     UNIQUEIDENTIFIER = (SELECT TOP 1 job_id FROM msdb..sysjobs WHERE NAME = @jobName)
    ,       @runStatus INT

    /* error if invalid job name */
    IF @jobId IS NULL RAISERROR (N'Unknown job: %s.', 16, 1, @jobName);
    
    /* start the job */
    EXEC @runStatus = msdb..sp_start_job @job_id = @jobId;
    
    /* job failed to start */
    IF @runStatus = 1 RETURN -1;

    WHILE (DATEDIFF(second, @start, GETDATE()) < @maxWaitSeconds)
    BEGIN
        SELECT TOP 1 @runStatus = run_status
        FROM msdb..sysjobactivity
        LEFT JOIN msdb..sysjobhistory ON sysjobactivity.job_history_id = sysjobhistory.instance_id AND step_id = 0
        WHERE sysjobactivity.job_id = @jobId AND run_requested_date >= @start
        ORDER BY run_requested_date ASC

        IF @runStatus IS NOT NULL
        BEGIN
            PRINT 'Job completed in '+CONVERT(VARCHAR(20),DATEDIFF(second, @start, GETDATE()))+' seconds with run_status = '+CONVERT(VARCHAR(20),@runStatus)
            BREAK
        END

        PRINT 'Job is still running after '+CONVERT(VARCHAR(20),DATEDIFF(second, @start, GETDATE()))+' seconds...'
        WAITFOR DELAY '0:0:05';
    END

    IF @runStatus IS NULL
    BEGIN
        PRINT 'Job did not complete before '+CONVERT(VARCHAR(20),@maxWaitSeconds)+' seconds elapsed.'
        SET @runStatus = -2
    END

    RETURN @runStatus
END

Answer 2

@lapponiandevil has the best, most usable solution here but their code is slightly more complex than it needs to be and doesn't actually work as-is since it needs @Time_constraint and @ok variables which aren't defined in the code shown.

These are to support a timeout for the Agent Job, but this isn't necessary. An Agent Job step can be configured with its own timeout value and will error out properly if it exceeds it, unlike their code. If you used that timeout method, you could find yourself hunting for phantom errors or being unaware that the Agent Job you were waiting for was still running as you moved to the next step in your process.

I think cutting the code down to the bare minimum and removing the timeout functionality is ideal. Here's what I came up with based on their solution:

-- Start Agent Job
DECLARE @JobName NVARCHAR(128) = 'My Agent Job Name'
   EXEC msdb.dbo.sp_start_job @JobName

-- Wait for Agent Job to finish
DECLARE @HistoryID AS INT = NULL
  WHILE @HistoryID IS NULL
  BEGIN
        --Read most recent Job History ID for the specified Agent Job name
            SELECT TOP 1 @HistoryID = b.job_history_id
              FROM msdb.dbo.sysjobs a
        INNER JOIN msdb.dbo.sysjobactivity b ON b.job_id = a.job_id
             WHERE a.name = @JobName
          ORDER BY b.Start_execution_date DESC
        --If Job is still running (Job History ID = NULL), wait 3 seconds
                IF @HistoryID IS NULL WAITFOR DELAY '00:00:03'
    END

-- Check Agent Job exit code to make sure it succeeded
     IF (SELECT run_status 
           FROM msdb.dbo.sysjobhistory 
          WHERE instance_id = @HistoryID) <> 1 
  THROW 69000, 'Child Agent Job failure', 1;  

Answer 3

Here is a a script that will check the status of a job and will run it if it is not running already.

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'

Answer 4

I might be a bit late but I found that the following query worked for me. It will give execution time and execution end time. You can alter it to get status as well.

SELECT 
    job.name, 
    job.job_id, 
    job.originating_server, 
    activity.run_requested_date, 
    activity.stop_execution_date, 
    DATEDIFF( SECOND, activity.run_requested_date, activity.stop_execution_date ) as Elapsed 
FROM msdb.dbo.sysjobs_view job 
JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id 
JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id 
JOIN 
( 
    SELECT 
    MAX( agent_start_date ) AS max_agent_start_date 
    FROM 
    msdb.dbo.syssessions 
) sess_max 
ON sess.agent_start_date = sess_max.max_agent_start_date 
WHERE run_requested_date IS NOT NULL 
--AND stop_execution_date IS NULL 
AND job.name = @JobName

Answer 5

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 6

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.