April 16, 2012 at 6:22 pm
Hello ,
Can you suggest me how to Receive a mail notification, not as soon as job completes but at a particular time after the job completion. In the job setup window, I just have an option in notifications as ' when job succeeds'. I want to get notified in the morning after job succeeds (job runs at night). May be I was thinking that I can use jobhistory tables (job_status column) and write a script such that if status is success, notify operator at particular time. Any suggestions would be appreciated.
April 17, 2012 at 1:23 am
You can't configure that in the job itself, so I would just create another job that runs in the morning to check if the other job succeeded or not.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 17, 2012 at 7:06 am
Yeah I understand that but i need help with scripting. here is my code:
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()
[[[[--- i need help here .how do i write sumtng such that if all run staus for this job is success, then i want to send mail]]]]]]
If jh.run_status = 1
EXEC master.dbo.xp_sendmail
@recipents=
,@message=' All hourly jobs successfull on getdate()'
April 17, 2012 at 7:18 am
This should get you started:
Querying SQL Server Agent Job Information[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply