SQL Server Agent – How to Capture Job Exit Code

jobssql-server-2017sql-server-agentssms

I'm essentially trying to find a better way to determine if my job has completed successfully before moving on to another call of the same job. I have multiple jobs which run through a parsing process before calling this one job in particular, but I don't want all the job calls to clash and ruin everything. What I have in place now to account for this looks like:

This basically just checks to see if the job_ID persists in the sys_jobs_view and sysjobactivity. If so, wait. If not, break and execute job again.

WHILE EXISTS (SELECT
            job.Name, job.job_ID
            ,job.Originating_Server
            ,activity.run_requested_Date
            ,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
        FROM
            msdb.dbo.sysjobs_view job 
                INNER JOIN msdb.dbo.sysjobactivity activity
                ON (job.job_id = activity.job_id)
        WHERE
            run_Requested_date is not null 
            AND stop_execution_date is null
            AND job.name like 'myJob%')
        BEGIN 
            WAITFOR DELAY '00:00:05.000'
        END 
EXEC msdb.dbo.sp_start_job 'myJob'

Is there a way to just capture the exit code and have something that looks like:

pesudo-code:

WHILE (*Capture Exit code for 'myJob'* = In_Progress)
BEGIN
    WAITFOR DELAY '00:00:05.000'

    IF (*Capture Exit code for 'myJob'* = Fail)
    BEGIN
        BREAK
    END
END

IF (*Capture Exit code for 'myJob'* = Success)
BEGIN
    EXEC msdb.dbo.sp_start_job 'myJob'
END

Best Answer

If you create a FUNCTION and query the sysjobhistory table in MSDB you can get a simple response on the most recent job execution.

Create the function:

CREATE FUNCTION [dbo].[fnGetLatestAgentJobExitCode]
(
    @JobName VARCHAR(100)
)
RETURNS VARCHAR(25)
AS
BEGIN
    RETURN(
        SELECT TOP (1)
            CASE jh.run_status
                WHEN 0 THEN 'Failed'
                WHEN 1 THEN 'Succeeded'
                WHEN 2 THEN 'Retry'
                WHEN 3 THEN 'Canceled'
                WHEN 4 THEN 'In Progress'
            END AS [Status]
        FROM [msdb].[dbo].[sysjobactivity] ja
        INNER JOIN [msdb].[dbo].[sysjobs] j ON j.job_id = ja.job_id
        INNER JOIN [msdb].[dbo].[sysjobhistory] jh ON jh.job_id = j.job_id AND jh.instance_id = ja.job_history_id
        WHERE j.[name] = @JobName
        ORDER BY ja.start_execution_date DESC)
END

Then query it like so:

SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob')

This will give one of the following results:

Failed
Succeeded
Retry
Cancelled
In Progress

Or will return no result if there is no job history. To use this in your specific use case would be simple:

WHILE (SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob') = 'In Progress')
BEGIN
    WAITFOR DELAY '00:00:05.000'

    IF (SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob') = 'Failed')
    BEGIN
        BREAK
    END
END

IF (SELECT [dbo].[fnGetLatestAgentJobExitCode]('myJob') = 'Succeeded')
BEGIN
    EXEC msdb.dbo.sp_start_job 'myJob'
END
Related Question