October 26, 2011 at 12:36 am
I have a scheduled job which runs every 5 minutes.
Last night the job continually failed due to a password change on an external system. This resulted in a notification email being sent to me every 5 minutes.
Is there a way to configure notifications to only send an email on the first failure and not send any more until after the job has succeeded again ?
Appreciate any ideas.
Geoff
October 26, 2011 at 7:11 am
You could do that by having a failure launch a step that would alter the job to stop sending notifications.
Check out sp_update_job for details on how you could do that.
You'd just have to set the steps in the job to have an "On Failure" path of going to a step that would reset the job alert.
NOTE: I haven't tried this. You'll need to really test it thoroughly to make sure it resets correct, still sends 1 alert, and so on. I haven't tried to do so, so I'm not sure how functional it will be, nor how difficult/easy it will be to set up.
If you do try it, please let us know how it goes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 10:47 am
Of course the issue is that you'd then have to have a step on Success that makes sure the job is updated again to send an email for the next failure.
You could also set the On Failure to a step that raise a custom error. Then create an Alert for that error and set a large delay between responses so you get an email once a day or once every 2 hours whatever.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 26, 2011 at 11:17 am
And, theoretically, if you turn notifications back on automatically due to a successful run, you'd probably want to send an e-mail alerting you to that. "Alerts re-activated after successful run", so that you have a trail and can see when it fixed itself.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 2:16 pm
Thanks for the ideas, will try it and let know how I go.
Geoff
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply