August 19, 2009 at 7:44 am
Hi all,
I'm running a SQL server 2k sp4 server and I've got (what I think is) a simple question.
Is there any way (either via T-SQL or the GUI) to configure a job to alert a specific user when the job itself is cancelled?
Many thanks!
Iain
August 19, 2009 at 8:22 am
There isn't a way I know of to do exactly that, but if you query the job schedule in msdb, you should be able to compare the times it was supposed to run vs the times it finished running, and send an alert based on that. That would require another job, and if you scheduled it to run on a short enough interval, you'd get something that would probably accomplish what you need.
Or you could get a copy of RedGate's SQL Response (I think that's the product), which will alert you to unrun jobs and such. No re-inventing the wheel that way, but it does cost money. I think that'll work on SQL 2000, I know it works on 2005 quite nicely.
- 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
August 19, 2009 at 8:50 am
Thanks for the quick response!
What if the job has been disabled? Is it possible to query a job and have it fire an alert if a job is disabled?
Regards,
Iain
August 20, 2009 at 6:57 am
All the job data is in the msdb database. I haven't used SQL 2000 in a couple of years, so I'm a little rusty on where to look, but look for tables like sysjobs and sysjobshistory in the system tables section of that database. Those should have the data you're looking for.
If you query those, you can work out when the job should have run, then you can query when it did run, and you can join the two and get a list of when it was supposed to but didn't. From that, you can raise alerts.
It'll be a custom process, unless you can Google/Bing/whatever a script that someone's already put together for that. Check out the scripts section of this site, it might have something.
- 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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply