SQL Job Notifications

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 =  '<FONT FACE="color: #A0522D; font-family: Verdana; font-size: 12; font-weight:bold">' + @@servername + '-Job: ' + sysjobs.name + '. Step= ' + 

     inserted.step_name + '</font><br><br><FONT FACE="color: #A0522D; font-family: Verdana; font-size: 12">Message ' + inserted.message + '</font>'

     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' +@@Servername

    from inserted 

    join sysjobs 

    on inserted.job_id = sysjobs.job_id

    where inserted.run_status = 0

 

--recipeints text

    IF @subject like '%<USE NAME OF FAILED JOB HERE>%'

        Begin 

            SET @strRecipient= '<RECPIENT LIST>'

        End

    ELSE

        Begin

            SET @strRecipient= '<ALTERNATE RECIPIENT LIST>'

        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

One thought on “SQL Job Notifications”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s