-- Stored Procedure: dbo.log_job_completion -- Bill Wunder use admin GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[log_job_completion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[log_job_completion] GO CREATE PROCEDURE [dbo].[log_job_completion] @notify_recipient_list varchar(255) AS /******************************************************************************************************* * admin.dbo.log_job_completion * Creator: bw * Date: * * Outline: Procedure will write the most recent failure message saved to msdb.dbo.sysjobhistory * to a logging table if the step preceeding the step that calls this procedure failed. This will * allow the message to remain available without causing job slowness by keeping a large number * of historical rows in msdb.dbo.sysjobhistory. * Procedure will also optionally notify any specified persons by email with the job * name and failure or completion (success( message. This will remove the need for use of SQLMail and * sysoperator for job failure notification to avoid memory leaks and Outlook client headaches of SQLMail. * * usage: EXECUTE admin.dbo.log_job_completion 'name@domain.com' * Notes: Set the "On Failure" job flow action of all job steps that would be set to "End Job Reporting * Failure" to go to a job step that calls this procedure. Make the step that calls this procedure * the last step in the job and set the step just before this one to "Go to Next Step" This will * allow a completion message to be sent if the job finishes normally. * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * bw 11-01-03 put in template, add error handling ********************************************************************************************************/ --------------------------------------------- -- declare variables --------------------------------------------- declare @message_text varchar(1024) -- actually nvarchar in msdb.dbo.sysjobhistory , @subject_line varchar(100) , @step_name sysname , @job_name sysname , @ec int , @rc int --------------------------------------------- -- create temp tables --------------------------------------------- --------------------------------------------- -- set session variables --------------------------------------------- SET NOCOUNT ON --------------------------------------------- -- body of stored procedure --------------------------------------------- -- create table if necessary if object_id('dbo.agent_job_log','U') is null CREATE TABLE [dbo].[agent_job_log] ( [id] [int] IDENTITY (1, 1) NOT NULL , [job_name] [sysname] NOT NULL , [step_name] [sysname] NOT NULL , [message] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [RecCreatedDt] [datetime] NULL CONSTRAINT [dft_agent_job_log__RecCreatedDt] DEFAULT (getdate()), PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] ) ON [PRIMARY] -- body of stored procedure select top 1 @step_name = h.step_name, @message_text = h.message, @job_name = j.name from msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j on h.job_id = j.job_id where substring(APP_NAME(), charindex('0x',APP_NAME()) + 18, 16) = substring(replace(h.job_id, '-',''),17,16) order by h.run_date desc, h.run_time desc if (@notify_recipient_list is not null) begin if (@message_text like ('% succeeded%')) begin -- send sucessful completion message select @subject_line = 'JOB succeeded: ' + @job_name -- use the xp_smtp_sendmail hook if available otherwise risk the call to xp_sendmail exec @rc = sysmon.dbo.safe_sendmail @recipients = @notify_recipient_list, @subject = @subject_line, @message = @message_text set @ec = @@error -- raise a level 16 error so the job status will indicate the failure if @rc <> 0 or @ec <> 0 Raiserror ('Job Failed! Notification Failed during send. See admin.dbo.agent_job_log for additional information',16,1) end else -- send job failed message and log error begin insert admin.dbo.agent_job_log (job_name, step_name, message) values (@job_name, @step_name, @message_text) select @subject_line = 'JOB FAILED: ' + substring(@job_name + ' (step: ' + @step_name + ')',1,87) -- use the xp_smtp_sendmail hook if available otherwise risk the call to xp_sendmail exec @rc = sysmon.dbo.safe_sendmail @recipients = @notify_recipient_list, @subject = @subject_line, @message = @message_text set @ec = @@error -- raise a level 16 error so the job status will indicate the failure if @rc <> 0 or @ec <> 0 Raiserror ('Job Failed! Notification Failed during send. See admin.dbo.agent_job_log for additional information',16,1) else Raiserror ('Job Failed! Notification Sent. See admin.dbo.agent_job_log for additional information',16,1) end end RETURN RETURN GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO