February 24, 2009 at 2:38 am
Hi all,
I know how to set up the notification by e-mail when a job fails or succeeds, but I have a requirement, if it at all possible, to receive a notification by e-mail when a STEP fails, without quitting the entire job (which would then notify on job failure) Is this possible in SQL2005 ??
February 24, 2009 at 2:43 am
Add a step in between which does the job of sending mail. It should work!
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 24, 2009 at 5:51 am
Thanks, I'll try and work one out 🙂
February 24, 2009 at 8:29 am
This would actually be fairly simple as you would put the on failure action of the step to GO to Step "Email" and on the email step you set the On Success Action to Go To "Step after failed step". The only issue is that it would not be generic, you would have to have an email step for each step in the job.
Another option would be to have the final step of the job be an email step that queries the msdb.dbo.sysjobhistory table and returns any steps for that job that failed. The query would look something like this:
SELECT
J.[name],
S.step_name,
S.message
FROM
dbo.sysjobs AS J Join
dbo.sysjobhistory AS S ON
J.job_id = S.job_id
WHERE
J.[name] = '[job name]' AND
-- this is failed
S.run_status = 0 AND
-- this eliminates the master job status
S.step_id <> 0
Use that as the query parameter for xp_sendmail in 2000 and sp_send_dbmail in 2005+
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
February 24, 2009 at 8:34 am
Wow Jack !, I like the look of that. The second option certainly looks interesting and am going to try both of them. Thanks.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply