July 3, 2003 at 2:09 pm
I wonder if anyone comes up with any simple and elegant solution to notify DBA if jobs fail in MSDE 2000. It is because SQLMail is not available in MSDE and I can't afford to spend $$ on 3rd party tool. Many thanks.
July 3, 2003 at 9:46 pm
Provided you SQL Server can see an SMTP mail server, you could have a scheduled job that runs regularly checking for recently failed jobs. Results, if any, can be mailed out through a number of SMTP mail facilities: MS CDOsys objects; or freeware 3rd party mailers such as XP_SMTP or BLAT.
Cheers,
- Mark
July 4, 2003 at 6:58 am
Any idea where the job status is kept ? I mean in my scheduled job, I still need to select some status from somewhere. Thanks.
July 4, 2003 at 7:44 am
You need to join msdb..sysjobhistory with msdb..sysjobs and look at sysjobhistory.run_status for step 0. Steps 1 onwards apply to the individual job steps whereas step 0 pertains to the job status. a run_status of 1 indicates success, and BOL will tell you what the other values mean.
Just to keep you on your toes, MS has retained decimal values for run_date (yyyymmdd) and run_time (hhmmss) rather than a single DATETIME value. This will make your code trickier. Send some hate mail to the SQL development team.
Cheers,
- Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply