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

  • 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,

  • 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.

  • That is what I was afraid of.

  • 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.

  • 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