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/
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
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 |
Continue reading on SirSQL.net.