Post reply

SQL Agent Notifications via DB Mail, how to change the subject line

  • February 23, 2009 at 3:02 pm

    #126658

    I am setting up notification in SQL2005 for job failures to be sent via email to a group set up in Database Mail. The email that comes in will have a subject line of:

    SQL Server Job System: 'Test' completed on Servername

    If I look into the body of the email, I will see that the job failed.

    Is there a way to change the subject line ..I would really like it to have Job Failed in there somewhere.

    I tried playing around with the Pager Email subject line, but that seems to have no effect on this.

    Thanks,

  • February 24, 2009 at 6:13 am

    #949279

    I don't know of a way to do this other than creating a job that checks the status of other jobs and uses sp_send_dbmail to send out an email for failed jobs.

    I know that I typically only have emails sent out when a job fails so I know when I get an email that it was because the job failed.

    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 9:13 am

    #949473

    That is what I was afraid of.

  • February 24, 2009 at 10:54 am

    #949539

    Hi John,

    I think the standard email subject line for send email on failure is:

    " DB Maintenance Plan Failed"

    I don't know a way of changing this behaviour, but there is another method you might want to look into. Instead of using SQL Agent mail for alerting you , you could try adding an extra step to your job that calls a script to send out the email. The way to do this is in the advanced tab of the job step, for your current last step, change the failure action from "Quit the job reporting failure" to "Goto the next step" and leave the success action as "Quit the job reporting success".

    Now create the new last step, use xp_cmdshell to run your email script (vbscript perhaps?) and set both the On Success and On failure advanced options of this step to "Quit the job reporting failure" to ensure that a failure is logged in the job history.

  • May 26, 2011 at 6:00 am

    #1330210

    Hi Stobe,

    Thanks for the detail, It work just what I was looking for.

    Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply