Trigger to Check Job failures

I was trying to find a alert which could fire immediately upon any of the job failure in the server to send an email. Unfortunately I couldn’t find any the alert related to this. So I thought of writing a DML trigger which should fire immediately when an entry is inserted in sysjobhistory table on msdb database. The next step is that the trigger should send a mail when there is failure in the job, hence Ive used run_status column in the table, as all the DBAs know when this column has a value 0 then it states the job has been failed. Ive tried this script in SQL Server greater than version 2000 however its not working on it. This script works only on SQL Server 2000, still some of them are still working on SQL 2000 so I thought this script has still value. This DML trigger will send an email upon any of the job failure.

Applies To

• SQL Server 2000

Script

In the script below make sure to change the mail address used in the recipients field. You also need SMTP mail to be configured in SQL Server to work however you can modify the script to include CDO messaging.

/*
Written By : Vidhya Sagar
www.sql-articles.com
*/
USE MSDB
GO
CREATE TRIGGER jobfailurenotify
ON sysjobhistory
FOR INSERT
AS
DECLARE @failed TINYINT, @subject VARCHAR(300), @body VARCHAR(500)
SELECT @failed = COUNT(*) from inserted where run_status=0
IF @failed > 0
BEGIN 
    SELECT @subject=name from msdb.dbo.sysjobs where job_id in (select distinct(job_id) from inserted where run_status=0)
    SET @subject=@subject + ' job failed on ' + CONVERT(VARCHAR(25), GETDATE())
    SET @body=@subject + '. Please look in to this. Server Name : ' + CONVERT(VARCHAR(25),SERVERPROPERTY('SERVERNAME'))
    EXEC master.dbo.xp_sendmail
    @recipients     = N'xxxxx@gmail.com',
    @subject  = @subject,
    @message     = @body
END
ELSE 
BEGIN
    PRINT 'No job failures found'
END

Sample Mail Output

From: testing@gmail.com

Sent: Monday, March 02, 2009 6:50 PM

To: xxxxx@gmail.com

Subject: TESTING job failed on Mar 2 2009 6:50PM

TESTING job failed on Mar 2 2009 6:50PM. Please look in to this. Server Name : SAGARSYS

If you have any concerns or queries regarding this script. Just post it out in our Forums Section.