USE msdb
GO
IF OBJECT_ID('trg_sysjobhistory_email_failure') IS NOT NULL
DROP TRIGGER trg_sysjobhistory_email_failure
GO
CREATE TRIGGER trg_sysjobhistory_email_failure ON [dbo].[sysjobhistory]
FOR INSERT
AS
-- ==========================================================================================================
-- Author: Gokhan Varol
-- Create date: 09/30/2010
-- Description: Generates an email to the operator defined in the job the error detail of a failed job step
-- ==========================================================================================================
DECLARE @job_id UNIQUEIDENTIFIER,
@job_name SYSNAME,
@step_id INT,
@step_name SYSNAME,
@run_date INT,
@run_time INT,
@run_duration INT,
@RunStart DATETIME
DECLARE @body VARCHAR(MAX),
@subject VARCHAR(8000),
@operator_name VARCHAR(256),
@email_address VARCHAR(256)
SELECT @job_id = jh.job_id,
@job_name = j.[name],
@step_id = jh.[step_id],
@step_name = jh.[step_name],
@operator_name = so.[name],
@email_address = so.[email_address],
@run_date = jh.[run_date],
@run_time = jh.[run_time],
@run_duration = jh.[run_duration],
@RunStart = CAST(CAST(jh.[run_date] AS VARCHAR) AS DATE)
+ CAST(CAST(STUFF(STUFF(RIGHT('0' + CAST(jh.[run_time] AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':') AS TIME) AS DATETIME)
FROM inserted jh (NOLOCK)
INNER JOIN [msdb].[dbo].[sysjobs] j (NOLOCK) ON jh.job_id = j.job_id
INNER JOIN [msdb].[dbo].[sysoperators] so (NOLOCK) ON j.notify_email_operator_id = so.id
WHERE j.notify_level_email & 2 = 2
AND jh.run_status = 0
AND so.enabled = 1
AND so.email_address <> ''
AND jh.step_id > 0
IF @@ROWCOUNT = 0
RETURN
SET @subject = 'Failed Job Output: ' + @job_name + ', step_id: ' + CAST(@step_id AS VARCHAR) + ', step_name: ' + @step_name
SET @body = 'job_name: ' + @job_name + '
step_id: ' + CAST(@step_id AS VARCHAR) + '
step_name: ' + @step_name + '
run_start: ' + CONVERT(VARCHAR(30), @RunStart, 120) + '
run_duration: ' + CAST(@run_duration AS VARCHAR) + '
job_id: ' + CAST(@job_id AS VARCHAR(100)) + '
notify operator by email: ' + @operator_name + '
'
SELECT @body = @body + MESSAGE
FROM [msdb].[dbo].[sysjobhistory] jh (NOLOCK)
WHERE run_status IN (0, 4)
AND step_id = @step_id
AND job_id = @job_id
AND run_date = @run_date
AND run_time = @run_time
ORDER BY instance_id
EXEC msdb.dbo.sp_send_dbmail
@recipients = @email_address,
@subject = @subject,
@body = @body
GO