January 28, 2011 at 12:01 pm
Hi All,
I have written some code to get the current status of a job. Please suggest if there is a simpler way to do this.
DECLARE @isJobSuccessful bit
DECLARE @runStatus smallint
SET @isJobSuccessful=1
IF EXISTS (
SELECT * FROM dbo.sysjobhistory sjh
INNER JOIN sysjobs sj ON sj.job_id = sjh.job_id
WHERE sj.name = 'test' AND step_name = '(Job outcome)' )
BEGIN
IF NOT EXISTS
(SELECT * FROM dbo.sysjobhistory sjh
INNER JOIN sysjobs sj
ON sj.job_id = sjh.job_id
WHERE sj.name = 'test'
AND sjh.instance_id > (SELECT MAX(instance_id)
FROM sysjobhistory sjh1
WHERE sjh1.step_name = '(Job outcome)' AND sjh1.job_id = sj.job_id ))
BEGIN
SELECT @runStatus=run_status FROM dbo.sysjobhistory sjh
INNER JOIN sysjobs sj
ON sj.job_id = sjh.job_id
WHERE sj.name = 'test'
AND sjh.instance_id = (SELECT MAX(instance_id)
FROM sysjobhistory sjh1
WHERE sjh1.step_name = '(Job outcome)' AND sjh1.job_id = sj.job_id)
IF @runStatus=1
SET @isJobSuccessful=0
END
END
PRINT 'Is Job Successful? ' + CASE @isJobSuccessful WHEN 1 THEN 'FALSE' ELSE 'TRUE' END
PRINT 'Status: ' + CASE @runStatus WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Successful'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' ELSE 'Not Known' END
PRINT @runStatus
Thanks and Regards,
Praveena
January 28, 2011 at 12:35 pm
You may find this script[/url] useful.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
January 28, 2011 at 1:50 pm
Pradeep,
I tried it earlier. Where the job has like 5 steps and say the 2nd step is running, it gives that the job step 1 has completed successfully. It doesn't give that Step 2 has been running. I think, only after a step is completed it is logged into the sysjobhistory.
January 28, 2011 at 1:52 pm
George,
I do not want to use an undocumented extended stored procedure. I am not sure if that is reliable.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply