Agent Jobs

Create A Trigger On The SysJobHistory Table To Execute A Job

Here’s some sql logic that will create a Trigger on the SysJobHistory table. The Trigger is designed to execute a Job whenever a *Failure* message has been inserted into the table.


use msdb;
set nocount on
set ansi_nulls on
set quoted_identifier on
go
create trigger [dbo].[check_for_job_failure] on msdb..sysjobhistory
after insert
as
begin
set nocount on
declare @is_fail int
set @is_fail = (select case when [message] like '%The step failed%' then 1
else 0 end from msdb..sysjobhistory where instance_id in (select max(instance_id) from
msdb..sysjobhistory))
if @is_fail = 1
begin
exec msdb.dbo.sp_start_job @job_name = 'SEND SQL JOB ALERTS (beta)'
end
end
 
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s