SQL Server Performance

Trigger: checkForJobFail

Discussion in 'Contribute Your SQL Server Scripts' started by Anonymous, Aug 1, 2006.

  1. Anonymous New Member

    Hii all,<br />i wld seriously appriciate ur comments<br /><br />/*<br />We do not use SQL mail on our SQL servers. So, we developed this trigger on sysjobhistory<br />to notify us of job failures.<br />We use xp_smtp_sendmail which is available free from<a target="_blank" href=http://www.sqldev.net/xp/xpsmtp.htm>http://www.sqldev.net/xp/xpsmtp.htm</a><br />*/<br />use msdb<br />SET QUOTED_IDENTIFIER OFF <br />GO<br />SET ANSI_NULLS ON <br />GO<br />if exists (select * from sysobjects where name = 'checkForJobFail')<br />drop TRIGGER [checkForJobFail]<br />go<br />CREATE TRIGGER [checkForJobFail] ON [dbo].[sysjobhistory] <br />FOR INSERT<br />AS<br /><br />declare @msg varchar(204<img src='/community/emoticons/emotion-11.gif' alt='8)' />, @maillist varchar(256), @mailfrom varchar(256), @jobName sysname, @subject varchar(256)<br />if (select step_id from inserted) = 0 and (select run_status from inserted) = 0<br />begin<br />select @maillist = 'yourDBA@yourcompany.com'<br />select @mailfrom = 'DBAlert@' + @@servername + '.yourcompany.com'<br />select @msg = message, @jobName = name<br />from inserted h join sysjobs j on h.job_id = j.job_id<br />select @subject = 'job ''' + @jobName + ''' failed on ' + @@servername<br />exec master.dbo.xp_smtp_sendmail<br /> @FROM = @mailfrom,<br /> @TO = @maillist,<br /> @priority = N'HIGH',<br /> @subject = @subject,<br /> @message = @msg,<br /> @server = N'mail.yourcompany.com'<br /><br />end<br />GO<br />SET QUOTED_IDENTIFIER OFF <br />GO<br />SET ANSI_NULLS ON <br />GO<br /><br /><br /><br />next2none is what others feel about the maverick
  2. Madhivanan Moderator

    >>if (select step_id from inserted) = 0 and (select run_status from inserted) = 0

    You will get error if query returns more than one value


    Madhivanan

    Failing to plan is Planning to fail

Share This Page