NuRoN Consulting, INC.

Blog > SQL Tools > Databases > msdb > Triggers > dbo.trg_sysjobhistory_email_failure

Dependencies for SQL DML trigger: msdb.dbo.trg_sysjobhistory_email_failure

ReferencingFQN	ReferencingType	ReferencedFQN	ReferencedType	Depth	RedHierarchy
msdb.dbo.trg_sysjobhistory_email_failure	SQL DML trigger	msdb.dbo.sp_send_dbmail	Stored procedure	1	[msdb].[dbo].[sp_send_dbmail]
msdb.dbo.trg_sysjobhistory_email_failure	SQL DML trigger	msdb.dbo.sysjobhistory	Table	1	[msdb].[dbo].[sysjobhistory]
msdb.dbo.trg_sysjobhistory_email_failure	SQL DML trigger	msdb.dbo.sysjobs	Table	1	[msdb].[dbo].[sysjobs]
msdb.dbo.trg_sysjobhistory_email_failure	SQL DML trigger	msdb.dbo.sysoperators	Table	1	[msdb].[dbo].[sysoperators]
msdb.dbo.sp_send_dbmail	Stored procedure	msdb.dbo.get_principal_id	SQL scalar function	2	[msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[get_principal_id]
msdb.dbo.sp_send_dbmail	Stored procedure	msdb.dbo.sp_validate_user	Stored procedure	2	[msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sp_validate_user]
msdb.dbo.sp_send_dbmail	Stored procedure	msdb.dbo.sysmail_principalprofile	Table	2	[msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sysmail_principalprofile]
msdb.dbo.sp_send_dbmail	Stored procedure	msdb.dbo.sysmail_verify_profile_sp	Stored procedure	2	[msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sysmail_verify_profile_sp]
msdb.dbo.sysmail_verify_profile_sp	Stored procedure	msdb.dbo.sysmail_profile	Table	3	[msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sysmail_verify_profile_sp], [msdb].[dbo].[sysmail_profile]

SQL DML trigger: msdb.dbo.trg_sysjobhistory_email_failure

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

Description for SQL DML trigger: msdb.dbo.trg_sysjobhistory_email_failure

Todo