April 16, 2012 at 6:01 pm
Hello
I have a job with three steps which is scheduled for every one hour from 2 a.m. to 1 a.m. everyday. i had setup job failure notification to an operator. I want to set up a job success email notification once everyday around 2 a.m next day, if all the hourly scheduled job (23 times) are success. i do know that i have to use msdb job history tables to do this. any help with scripting is really appreciated.
April 17, 2012 at 7:14 am
here is the code: any modifications is welcum
select * from (
select j.name
,Run_Date=convert(datetime,convert(varchar(8),jh.run_date))
,duration_in_Secs=jh.run_duration/10000*3600
+jh.run_duration%10000/100*60
+jh.run_duration%100
,jh.run_status
from msdb.dbo.sysjobhistory jh
join msdb.dbo.sysjobs j on jh.job_id = j.job_id
and j.name = 'storedprocedures') as J
where Run_Date between GETDATE()-1 and GETDATE()
order by j.Run_Date desc
If jh.run_status = 1(but i want all job status to be success, then only i want an email )
EXEC master.dbo.xp_sendmail
@recipents=
,@message=' All hourly jobs successfull on getdate()'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply