Search code examples
sql-serveremailjobs

SQL Server : change automatically generated Job Notification Email

Is it possible to change the automatically generated job notification email? In my case it generates a mail like this (translated by me):

Subject:

SQLServer-Jobsystem: 'JobName' at \\ServerName\DatabaseName finished.

Body:

JobExecution:   'JobName' executed at '12.12.2014' 14:48:04.
Duration:   0 Hours, 0 Minutes, 1 Seconds
STATUS:     Successfully Finished
Messages:   The Job was successful. The Job has been executed by Schedule (Su 7:00) Last Executed Step 1 (JobName).

I want to define the subject and body by my self, but I cant find any stored procedures for this.

Solution

  • I know of no way to modify the built in emails SQL Agent sends. The template is hard coded, as far as I'm aware.

    You could use customized notifications. Basically, add a step after the last step in SQL Agent, then use that step to send the success email. Since it's the last step and every previous step said "Success: Go to next" and "Failure: Quit reporting failure", the only way the last step is running is if everything was a success. Now your new step can itself send the email for success.

    You could, theoretically, do the same for failure notices, but the logic gets a bit hairy then since the're not very good branching logic support in SQL Agent.