------------------------------------------------------------------------------------------------------------------------------
--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).
--2012-09-14 Changed mail subject. Removed server name. Prior version still there, with comment.
--2013-08-06 Romain Casteres (http://www.pulsweb.fr/) made changes
------------------------------------------------------------------------------------------------------------------------------

--Exit if sqlmaint database doesn't exist
IF DB_ID('###') IS NULL
  RAISERROR('Database ### doesn''t exist, existing script', 21, 1) WITH LOG

USE ###
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 = 'BI4NEMO'
	,@mail_on_success char(1) = 'Y' --'Y', 'N'
	,@attach_output_file varchar(10) --= 'ON_FAILURE' --'ALWAYS', 'NEVER', 'ON_FAILURE'
AS
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)
,@command varchar(max)
,@ErrMessage varchar(max)
,@run_datetime datetime

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 @subject = CASE WHEN @run_status_desc = 'SUCCEEDED' THEN 'Ok' ELSE @run_status_desc END + ': ' + @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


------------------------------------------------------------------------------------------------------------------------------
--Print Error directy in the mail
------------------------------------------------------------------------------------------------------------------------------
IF (@run_status=0) 
BEGIN
	SELECT 
		@command=sysjobsteps.command, 
		@ErrMessage=sysjobhistory.message, 
		@run_datetime=convert(datetime,left(run_date,4)+'/'+substring(run_date,5,2)+'/'+right(run_date,2)+' '+ left(run_time,2)+':'+substring(run_time,3,2)+':'+right(run_time,2))
	FROM msdb..sysjobs sysjobs 
		INNER JOIN 
		(	SELECT 
				instance_id,
				job_id,
				step_id,
				step_name,
				sql_message_id,
				sql_severity,
				message,
				run_status,
				run_duration,
				operator_id_emailed,
				operator_id_netsent,
				operator_id_paged,
				retries_attempted,
				server,
				run_date= convert(varchar(8),run_date),
				run_time= case when len(convert(varchar(8),run_time))=5 then '0' + convert(varchar(8),run_time) else convert(varchar(8),run_time) end
		  FROM msdb..sysjobhistory) sysjobhistory ON sysjobs.job_id=sysjobhistory.job_id 
		INNER JOIN msdb..sysjobsteps sysjobsteps ON sysjobsteps.job_id=sysjobhistory.job_id AND sysjobsteps.step_id=sysjobhistory.step_id
		INNER JOIN 
		(	SELECT job_id, instance_id = max(instance_id) FROM msdb..sysjobhistory WHERE job_id = @job_id AND step_id =0 GROUP BY job_id
			UNION 
			SELECT job_id, instance_id = Min(instance_id) FROM msdb..sysjobhistory WHERE job_id = @job_id AND NOT EXISTS (SELECT * FROM msdb..sysjobhistory WHERE job_id=@job_id AND step_id=0) GROUP BY job_id
		) sjh_Min ON sjh_Min.job_id =sysjobs.job_id 
		AND sysjobhistory.instance_id > sjh_Min.instance_id
	WHERE (sysjobs.job_id = @job_id) AND (sysjobhistory.step_id<>0)
	ORDER BY sysjobhistory.instance_id;

	SET @body = @body + char(13) +
		+ '--------------------------------------' + char(13) + 'Run Date : ' + convert(varchar(50), @run_datetime ) + char(13)
		+ '--------------------------------------' + char(13) + 'Command : ' + @command + char(13)
		+ '--------------------------------------' + char(13) + 'Error : ' + @ErrMessage
END

------------------------------------------------------------------------------------------------------------------------------
--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'
*/
------------------------------------------------------------------------------------------------------------------------------