I have set email notification on the completition of a scheduled job. Is it possible to customize the email content?
Alberto De Caro asked Aug 3, 2012 at 13:36 Alberto De Caro Alberto De Caro 359 2 2 gold badges 5 5 silver badges 14 14 bronze badgesThe way I've done this in the past is added a final step to the job, then I can use Database Mail to customize everything about the message (subject, body, recipients, from, etc) based on the server, the status/outcome of the job, and even the duration.
It is cumbersome if you have a lot of jobs, but I did this for a variety of other reasons as well (for example, other actions to perform in addition to sending an e-mail).
You may also consider a management tool like SQL Sentry which provides lots of flexibility over how to handle successful/failed jobs, and has comprehensive options for subsequent actions.
answered Aug 3, 2012 at 15:59 Aaron Bertrand Aaron Bertrand 181k 28 28 gold badges 401 401 silver badges 619 619 bronze badgesI don't understand. How does adding a final step to the job allow me to use Database Mail to customize the message? Perhaps you mean to write code in the final step the executes EXEC sp_send_dbmail in which we can customize the message. That makes sense.
Commented Apr 27, 2018 at 18:58@Baodad Because you can call sp_send_dbmail yourself, with whatever subject, body etc. as you want as a part of that job step instead of relying on the default alerting mechanisms Agent will do. You can even have multiple steps that do this, so that if step 1 fails, it calls step 7, which e-mails the people who care if step 1 fails. If step 1 succeeds it moves to step 2, and maybe that notification list is different, so step 2 on failure calls step 8, which has a different call to sp_send_dbmail. 7 and 8 wouldn't be reached on their own except through failure of a prior step.