Returning Status Results When Calling SQL Server Agent Jobs

One issue with kicking off jobs using the msdb procedure sp_start_job, is that it simply returns a result on whether the job has started successfully or failed to start. It doesn’t return the status on what the job executed. If there’s a failure in the job, you’re blind on the result. We recently have requirement where a front end java workflow process will call a SQL Agent job, and upon completion move on to the next java task. If a failure occurs we need java to log the error. In order to meet this need I wrote a stored procedure that encapsulates the job, and returns a success or failure return value. In the event of a failure it spits out the error message for java to consume. A bullet point of this procedure is:

• Grab greatest Instance_ID for the job in sysjobhistory table. This lets the procedure know when the job has finished because it will be greater than the last run upon completion
• A delay on the loop is provided for the calling application to determine how often to check the table the default is 500 ms
• If the job gets stuck and/or runs too long the calling app can pass in a timeout value to exit and kill the job
• \ In the event of failure lookup the last step to that failed and report that to the application.

Procedure Code:

USE [msdb]

GO

create procedure [dbo].[JobRun]

@Jobname varchar(500),

@LoopWait varchar(20) = '00:00:01',

@TimeLimit int = 600

as

Declare @historyId int

Declare @CurrentId int

Declare @ErrorMessage nvarchar(1024)

Declare @Jobid varchar(100)

Declare @Starttime datetime

Declare @RunningTime int

--Get starttime to determine if job is running too long

select @Starttime = getdate()

--Get JobId

select @Jobid = job_id from msdb.dbo.sysjobs

where name = @jobname

--Get instance_id as starting point

select @historyid = max(instance_id)

from msdb.dbo.sysjobs j

inner join msdb.dbo.sysjobhistory h

on j.job_id = h.job_id

where J.job_id = @jobid

--Set Baseline For Loop

set @CurrentId = @historyid

--Start Job

EXEC msdb.dbo.sp_start_job @Jobname

--Loop to determine if Job is complete

While @historyid = @CurrentId

Begin

--Checks completion

select @CurrentId = max(instance_id) from msdb.dbo.sysjobs j

inner join msdb.dbo.sysjobhistory h

on j.job_id = h.job_id

where J.name = @jobname

and step_id = 0

--Determine if Job has been running too long

select @RunningTime = datediff(ss, @starttime, getdate())

if @RunningTime > @TimeLimit

Begin

set @ErrorMessage = 'Job Ran for ' + convert(varchar(10), @RunningTime) + ' which is longer then the default baseline value of ' + convert(varchar(10),@TimeLimit)

Goto ExitJobRanToLong

End

-- Wait

waitfor delay @LoopWait

end

--If Job is successful Quit Reporting Success

if exists(

select * from msdb.dbo.sysjobhistory

where instance_id = @CurrentId

and run_status = 1)

Begin

goto ExitProcSuccess

end

--If Job is Failure go to Fail Tag

if exists(

select * from msdb.dbo.sysjobhistory

where instance_id = @CurrentId

and run_status = 0)

Begin

goto ExitProcFailure

end

ExitJobRanToLong:

Raiserror (@ErrorMessage, 16, 1)

--Kill Job

EXEC msdb.dbo.sp_stop_job @Jobname

Return (1)

ExitProcFailure:

--Find Failure Step and pass back error message

select top 1 @ErrorMessage = [message] from msdb.dbo.sysjobhistory

where job_id = @jobid

and step_id != 0

and run_status = 0

order by instance_id desc

--Raiserror with failure message

Raiserror (@ErrorMessage, 16, 1)

Return (1)

--Success Return 0

ExitProcSuccess:

RETURN (0)