SQL Job Notifications

SQL Server Best Practices
SQL Server Best Practices
Today on Twitter TJay Belt (blog|twitter) was asking about job notifications, specifically related to being able to add some kind of logic to that notification.

I’m using a trigger on the sysjobhistory in the msdb database to handle this, below is a version with very basic logic, but something easily expanded upon

This process is based upon the script posted by Kenneth Singer back in 2007 on SQL Server Central. You can find that at http://www.sqlservercentral.com/scripts/Miscellaneous/30177/

USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE   trigger [dbo].[trg_jobstepfailures]
on [dbo].[sysjobhistory]
for insert
as 

declare @strcmd varchar(800),@strRecipient varchar(500),@strMsg varchar(2000), @strServer varchar(255),@strTo varchar(255)
Declare @Subject varchar(500)

if exists (select * from inserted where run_status = 0 and step_name NOT IN ( '(job outcome)', 'do not notify'))
begin
    select  @strMsg =  '' + @@servername + '-Job: ' + sysjobs.name + '. Step= ' + 
     inserted.step_name + '

Message ' + inserted.message + '' from inserted join sysjobs on inserted.job_id = sysjobs.job_id where inserted.run_status = 0 --subject text select @subject = 'Job ' + sysjobs.name + ' Failed on Job Server' [email protected]@Servername from inserted join sysjobs on inserted.job_id = sysjobs.job_id where inserted.run_status = 0 --recipeints text IF @subject like '%%' Begin SET @strRecipient= '' End ELSE Begin SET @strRecipient= '' End exec msdb.dbo.sp_send_dbmail @profile_name = null --uses the default profile , @recipients = @strRecipient , @subject = @subject , @body = @strMsg , @body_format = 'HTML' --default is TEXT end GO

Continue reading on SirSQL.net.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *