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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy