dimanche 1 février 2015

MSSQL job to check the status of another job


I am looking to create a job which would check a status of another job every 5 minutes.



select
CASE jh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
END AS Run_Status
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobhistory jh on
j.job_id = jh.job_id
where j.name = 'DW Job'
group by j.name, jh.run_status


How would I go about creating a step to check the status of a job every 5 minutes and if it completes sucessfully, go to the next step, other wise keep checking if the job is running and if the previous job fails, then report a job failure.





Aucun commentaire:

Enregistrer un commentaire