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
SirSQL.net 
One thought on “SQL Job Notifications”