Home
About me and Nucleus
Partners
Articles, tips etc.
My blog
Links
Microsoft articles
Forums
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.
Versions etc.
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?
- 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 sucsessfully executed jobs.
- 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.
@job_id uniqueidentifierId 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_FAILUREUnder 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 placeolders
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)
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).
The code:
--Exit if sqlmaint database doesn't exist
IF DB_ID('sqlmaint') IS NULL
RAISERROR('Database sqlmaint doens''t exist, existing script', 21, 1) WITH LOG
USE sqlmaint
GO
IF OBJECT_ID('dbo.MailAfterJob') IS NOT NULL DROP PROC dbo.MailAfterJob
GO
CREATE PROC dbo.MailAfterJob
@job_id uniqueidentifier
,@strtdt varchar(100)
,@strttm varchar(100)
,@operator_name sysname = 'MSXOperator'
,@mail_on_success char(1) = 'Y' --'Y', 'N'
,@attach_output_file varchar(10) = 'ON_FAILURE' --'ALWAYS', 'NEVER', 'ON_FAILURE'
AS
------------------------------------------------------------------------------------------------------------------------------
--This will email outcome from job, and attach output file
--Written by Tibor Karaszi 2011-12-28
--2011-12-29 Tips from Ola Hallengren:
-- Fixed so it works if job not started on first step
-- Use fn_varbintohexstr to get hex representation of job as string
--2012-02-14 Represent agent token strings as binary, so there aren't replaced when pushed out from MSX server.
--2012-02-22 Fixed bug where we got error if no jobstep had outputfile.
--2012-02-28 Added support for agent tokens MACH (machine name) and INST (instance name).
------------------------------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE
@job_name sysname
,@job_id_str varchar(200) --GUID representation as string without hyphens
,@email_address nvarchar(300)
,@run_status int --0 = Failed, 1 = Succeeded, 2 = Retry, 3 = Canceled
,@run_status_desc varchar(9) --Failed, Succeeded, Retry, Canceled
,@importance varchar(6) --low, normal, high
,@output_file_names varchar(max)
,@subject nvarchar(255)
,@body nvarchar(max)
,@step_name sysname --to hold name of jobstep
,@step_duration int
,@step_duration_str varchar(20)
,@job_duration int
,@job_duration_str varchar(20)
,@step_id int
,@step_run_status int
,@step_run_status_desc varchar(9)
,@crlf char(2)
,@send_mail_bit bit --calculated just before send mail routine
,@attach_output_file_bit bit --calculated just before send mail routine
,@ag_tkn_step_id varbinary(200)
,@ag_tkn_job_id varbinary(200)
,@ag_tkn_strt_dt varbinary(200)
,@ag_tkn_strt_tm varbinary(200)
,@ag_tkn_mach_nm varbinary(200)
,@ag_tkn_inst_nm varbinary(200)
SET @crlf = CHAR(13) + CHAR(10)
SET @body = ''
------------------------------------------------------------------------------------------------------------------------------
--We can't represent agent tokens as strings if we want to push this proc out from an MSX server.
--The first SELECT is only used in dev, to ger each strin representation as varbinary.
-- This will look weird when deployed on a TSX server.
--The second part sets each variable to a varbinary representation of each string.
--The variable are used later in the proc
------------------------------------------------------------------------------------------------------------------------------
--SELECT
-- CAST('$(ESCAPE_SQUOTE(STEPID))' AS varbinary(200))
--,CAST('$(ESCAPE_SQUOTE(JOBID))' AS varbinary(200))
--,CAST('$(ESCAPE_SQUOTE(STRTDT))' AS varbinary(200))
--,CAST('$(ESCAPE_SQUOTE(STRTTM))' AS varbinary(200))
--,CAST('$(ESCAPE_SQUOTE(MACH))' AS varbinary(200))
--,CAST('$(ESCAPE_SQUOTE(INST))' AS varbinary(200))
SET @ag_tkn_step_id = 0x24284553434150455F5351554F5445285354455049442929
SET @ag_tkn_job_id = 0x24284553434150455F5351554F5445284A4F4249442929
SET @ag_tkn_strt_dt = 0x24284553434150455F5351554F5445285354525444542929
SET @ag_tkn_strt_tm = 0x24284553434150455F5351554F54452853545254544D2929
SET @ag_tkn_mach_nm = 0x24284553434150455F5351554F5445284D4143482929
SET @ag_tkn_inst_nm = 0x24284553434150455F5351554F544528494E53542929
------------------------------------------------------------------------------------------------------------------------------
--Validate input parameters
-------------------------------------------------------------------------------------------------------------------------------
IF @mail_on_success NOT IN('Y', 'N')
BEGIN
RAISERROR('Bad value for parameter @mail_on_success, values allowed are ''Y'' and ''N''.', 16, 1)
RETURN
END
IF @attach_output_file NOT IN ('ALWAYS', 'NEVER', 'ON_FAILURE')
BEGIN
RAISERROR('Bad value for parameter @attach_output_file, values allowed are ''ALWAYS'', ''NEVER'' andd ''ON_FAILURE''.', 16, 1)
RETURN
END
-------------------------------------------------------------------------------------------------------------------------------
--Get job name
------------------------------------------------------------------------------------------------------------------------------
SET @job_name = (SELECT s.name FROM msdb.dbo.sysjobs AS s WHERE s.job_id = @job_id)
IF @job_name IS NULL
BEGIN
RAISERROR('Failed to retreive job name baed on @job_id, teminating procedure MailAfterJob.', 16, 1)
RETURN
END
------------------------------------------------------------------------------------------------------------------------------
--String representation of job_id (to match representation in file name)
------------------------------------------------------------------------------------------------------------------------------
SET @job_id_str = UPPER(master.dbo.fn_varbintohexstr(@job_id))
------------------------------------------------------------------------------------------------------------------------------
--Get email_address for operator
-------------------------------------------------------------------------------------------------------------------------------
SET @email_address = (SELECT o.email_address FROM msdb.dbo.sysoperators AS o WHERE o.name = @operator_name)
IF @email_address IS NULL
BEGIN
RAISERROR('Unknown mail operator name, teminating procedure MailAfterJob.', 16, 1)
RETURN
END
------------------------------------------------------------------------------------------------------------------------------
--Get job outcome for *this* execuution, store in table variable
------------------------------------------------------------------------------------------------------------------------------
DECLARE @jobhistory table(instance_id int, step_id int, run_status int, step_name sysname, step_duration int)
INSERT INTO @jobhistory (instance_id, step_id, run_status, step_name, step_duration)
SELECT instance_id, step_id, run_status, step_name, run_duration
FROM msdb.dbo.sysjobhistory AS h
WHERE h.job_id = @job_id
AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= msdb.dbo.agent_datetime(CAST(@strtdt AS int), CAST(@strttm AS int))
------------------------------------------------------------------------------------------------------------------------------
--Get lowest run status for this execution (0 = fail)
------------------------------------------------------------------------------------------------------------------------------
SET @run_status = (SELECT MIN(h.run_status)
FROM @jobhistory AS h
INNER JOIN msdb.dbo.sysjobhistory AS hi ON hi.instance_id = h.instance_id
WHERE hi.job_id = @job_id)
IF @run_status IS NULL
BEGIN
RAISERROR('Could not determine run status for job, teminating procedure MailAfterJob.', 16, 1)
RETURN
END
SET @run_status_desc = CASE @run_status
WHEN 0 THEN 'FAILED'
WHEN 1 THEN 'SUCCEEDED'
WHEN 2 THEN 'RETRY'
WHEN 3 THEN 'CANCELED'
END
------------------------------------------------------------------------------------------------------------------------------
--Set importance for email
------------------------------------------------------------------------------------------------------------------------------
IF @run_status = 0
SET @importance = 'high'
ELSE
SET @importance = 'low'
------------------------------------------------------------------------------------------------------------------------------
--Get output file names to attach to email, in table variable
------------------------------------------------------------------------------------------------------------------------------
DECLARE @output_file_names_table table(output_file_name_step varchar(300))
INSERT INTO @output_file_names_table(output_file_name_step)
SELECT REPLACE(COALESCE(s.output_file_name, ''), CAST(@ag_tkn_step_id AS varchar(200)), CAST(s.step_id AS varchar(20))) AS out_file_name
FROM msdb.dbo.sysjobsteps AS s
WHERE s.job_id = @job_id
AND s.output_file_name IS NOT NULL
AND EXISTS(SELECT * FROM @jobhistory AS h WHERE h.step_id = s.step_id)
--Replace agent tokens with actual values
UPDATE @output_file_names_table SET output_file_name_step = REPLACE(output_file_name_step, CAST(@ag_tkn_job_id AS varchar(200)), @job_id_str)
UPDATE @output_file_names_table SET output_file_name_step = REPLACE(output_file_name_step, CAST(@ag_tkn_strt_dt AS varchar(200)), @strtdt)
UPDATE @output_file_names_table SET output_file_name_step = REPLACE(output_file_name_step, CAST(@ag_tkn_strt_tm AS varchar(200)), @strttm)
UPDATE @output_file_names_table SET output_file_name_step = REPLACE(output_file_name_step, CAST(@ag_tkn_mach_nm AS varchar(200)), CAST(SERVERPROPERTY('MachineName') AS varchar(100)))
UPDATE @output_file_names_table SET output_file_name_step = REPLACE(output_file_name_step, CAST(@ag_tkn_inst_nm AS varchar(200)), (ISNULL(CAST(SERVERPROPERTY('InstanceName') AS varchar(100)), '')))
--Loop table with file names, create semi-colon separated string
DECLARE @output_file_name_step varchar(300)
SET @output_file_names = ''
DECLARE c CURSOR FOR SELECT DISTINCT output_file_name_step FROM @output_file_names_table
OPEN c
WHILE 1 = 1
BEGIN
FETCH NEXT FROM c INTO @output_file_name_step
IF @@FETCH_STATUS <> 0
BREAK
SET @output_file_names = @output_file_names + @output_file_name_step + ';'
END
CLOSE c
DEALLOCATE c
--Remove the last semi-colon
IF LEN(@output_file_names) > 0
SET @output_file_names = SUBSTRING(@output_file_names, 1, LEN(@output_file_names) - 1)
------------------------------------------------------------------------------------------------------------------------------
--Construct email parts
------------------------------------------------------------------------------------------------------------------------------
--Set mail subject
SET @subject = @@SERVERNAME + ' ' + @run_status_desc + ' ' + @job_name
--Set mail body
DECLARE c cursor FOR SELECT h.step_id, h.step_name, h.step_duration, h.run_status FROM @jobhistory AS h ORDER BY instance_id
OPEN c
WHILE 1 = 1
BEGIN
FETCH NEXT FROM c INTO @step_id, @step_name, @step_duration, @step_run_status
IF @@FETCH_STATUS <> 0
BREAK
SET @step_duration_str = RIGHT('00000' + CAST(@step_duration AS varchar(6)), 6) --Make sure we have 0:s first
SET @step_duration_str = SUBSTRING(@step_duration_str, 1, 2) + ':' +
SUBSTRING(@step_duration_str, 3, 2) + ':' + SUBSTRING(@step_duration_str, 5, 2)
SET @step_run_status_desc = CASE @step_run_status
WHEN 0 THEN 'FAILED'
WHEN 1 THEN 'SUCCEEDED'
WHEN 2 THEN 'RETRY'
WHEN 3 THEN 'CANCELED'
END
IF @step_id <> 0
SET @body = @body + 'Step "' + @step_name + '" executed ' + @step_run_status_desc + ', time ' + @step_duration_str + '.' + @crlf
END
CLOSE c
DEALLOCATE c
SET @job_duration = (SELECT SUM(step_duration) FROM @jobhistory)
SET @job_duration_str = RIGHT('00000' + CAST(@job_duration AS varchar(6)), 6) --Make sure we have 0:s first
SET @job_duration_str = SUBSTRING(@job_duration_str, 1, 2) + ':' +
SUBSTRING(@job_duration_str, 3, 2) + ':' + SUBSTRING(@job_duration_str, 5, 2)
SET @body = 'Job executed, time ' + @job_duration_str + '.' + @crlf + @crlf + @body
------------------------------------------------------------------------------------------------------------------------------
--Decide whether to send email
------------------------------------------------------------------------------------------------------------------------------
IF (@mail_on_success = 'N' AND @run_status = 1) --1 = Success
SET @send_mail_bit = 0
ELSE
SET @send_mail_bit = 1
------------------------------------------------------------------------------------------------------------------------------
--Decide whether to attach output file
------------------------------------------------------------------------------------------------------------------------------
SET @attach_output_file_bit = 0
IF @attach_output_file = 'ALWAYS'
SET @attach_output_file_bit = 1
IF @attach_output_file = 'NEVER'
SET @attach_output_file_bit = 0
IF @attach_output_file = 'ON_FAILURE' AND @run_status <> 1 --1 = Success
SET @attach_output_file_bit = 1
------------------------------------------------------------------------------------------------------------------------------
--Send the email
------------------------------------------------------------------------------------------------------------------------------
IF @send_mail_bit = 1
BEGIN
IF @attach_output_file_bit = 0
EXEC msdb.dbo.sp_send_dbmail --Do no attach output file
@recipients = @email_address
,@subject = @subject
,@body = @body
,@importance = @importance
ELSE
EXEC msdb.dbo.sp_send_dbmail --Do attach output file
@recipients = @email_address
,@subject = @subject
,@body = @body
,@importance = @importance
,@file_attachments = @output_file_names
END
------------------------------------------------------------------------------------------------------------------------------
--Exit with fail if we got here on failure
------------------------------------------------------------------------------------------------------------------------------
IF @run_status = 0
RAISERROR('Job failed', 16, 1)
------------------------------------------------------------------------------------------------------------------------------
/*
--Sample execution, as to be defined in job (this can look weird when deployed on TSX server)
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' --'ALWAYS', 'NEVER', 'ON_FAILURE'
*/
------------------------------------------------------------------------------------------------------------------------------
GO