Overview
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.
The code
Versions
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?
- The mail subject will say whether the job was executed successfully. You don't have to read the body of the email. An example when this is particularly useful is when you in the morning want to delete all emails from successfully executed jobs. This was added to SQL Server 2012, although I find the mail subject a bit too verbose.
- The output file(s) from each job step will be attached to the email. This allows you to immediately start troubleshooting a failed job execution by looking at error messages in the output file.
- You will see execution time including status for each job step in the mail body. This allows you to immediately see which step that failed and also determine which step takes longest time to execute.
Parameters
@job_id uniqueidentifier
Id of job. This will be passed using an Agent Token. No default.
@strtdt varchar(100)
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.
@strttm varchar(100)
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.
@operator_name sysname = 'MSXOperator'
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'.
@mail_on_success char(1) = 'Y'
Whether to send email on successful execution. Allowed values are 'Y' and 'N'. Default is 'Y'.
@attach_output_file varchar(10) = 'ON_FAILURE
Under which circumstances to attach the output file. Allowed values are 'ALWAYS', 'NEVER' and 'ON_FAILURE'. Default is 'ON_FAILURE'.
Sample execution
EXEC sqlmaint.dbo.MailAfterJob
@job_id = $(ESCAPE_SQUOTE(JOBID))
,@strtdt = '$(ESCAPE_SQUOTE(STRTDT))'
,@strttm = '$(ESCAPE_SQUOTE(STRTTM))'
,@operator_name = 'MSXOperator'
,@mail_on_success = 'Y'
,@attach_output_file = '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).
Supported placeholders
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:- (JOBID)
- (STEPID)
- (STRTTM)
- (STRTDT)
- (MACH)
- (INST)
Where to add this? How to handle execution flow?
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 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". But you can also have it in master or msdb.