|MailAfterJob - send email after Agent job, include ouput files etc.|
About me and Nucleus
Articles, tips etc.
Courses and training
I have been wanting a more powerful email implementation after SQL Server Agent job execution for a long time now. Since that hasn't happened yet, I decided to roll my own.
This script was written and tested on SQL Server 2008 R2. It should work on 2008 and 2005 as well.
See comment block in procedure source code for version history of the procedure.
The final inspiration for writing this procedure came during a discussion with Ola Hallengren regarding his excellent maintenance procedures and the ability to send email after such job execution.
Why would you want to use it?
Id of job. This will be passed using an Agent Token. No default.
Start date of job execution. We need this since this might be used in output file naming. This will be passed using an Agent Token. No default.
Start time of job execution. We need this since this might be used in output file naming. This will be passed using an Agent Token. No default.
Name of operator to which we will send email. Note that this is operator name, not email address. Only one operator name is supported. Default is 'MSXOperator'.
Whether to send email on successful execution. Allowed values are 'Y' and 'N'. Default is 'Y'.
Under which circumstances to attach the output file. Allowed values are 'ALWAYS', 'NEVER' and 'ON_FAILURE'. Default is 'ON_FAILURE'.
You typically want to execute above as a T-SQL job step. Make sure you qualify the procedure with the proper database name (or specify the database name in the T-SQL job step).
In order to grab the right output file name for the job steps (from the msdb.dbo.sysjobsteps table), to attach to the email, we need replace whatever Agent Tokens were used when specifying the output file name. The supported tokens are:
You will most likely add this as a final job step for your jobs. I typically set prior job steps to "Goto next step" for both "On Success" and "On Failure", but this will depend on whether you want to break eary on failure.
This job step will likely have "Quit the job reporting success" for "On Success" and "Quit the job reporting failure" for "On Failure".
In which database?
I usually have some maintenance database for these types of things, typically named "sqlmaint" or "maint". But you can also have it in master or msdb, if you don't already have such database and don't like creating one for just this purpose (although it is in general not recommended to create user stuff in these system databases).
You find the code here.