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)
