-- Stored Procedure: dbo.log_job_error
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[log_job_error]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[log_job_error]
GO




CREATE PROCEDURE [dbo].[log_job_error] 
 @notify_recipient_list varchar(255) 
AS
/*******************************************************************************************************
* admin.dbo.log_job_error 
* Creator: Bill Wunder
* Date:   December 17, 2001
*
* Outline: Procedure will write the most recent failure message saved to msdb.dbo.sysjobhistory
*   to a logging table. 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 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_error 'name@domain.com'
* Notes:  Set the "On Failure" job flow action of all jobs that would be set to "End Job Reporting 
*                       Failure" to go to a job step that calls this procedure.
*   
* Modifications   
*         developer name     date     brief description
*         ------------------ -------- ------------------------------------------------------------
*         Bill Wunder        4-11-02  automate job name identifiaction, no longer need the precise parameter 
*         Bill Wunder        2-94-03  update usage to reflect above change
*         Bill Wunder       4-19-03  add support for xp_sendmail if safe_sendmail is not available 
*         bw                 9-24-03  readded the default to the recipient that mysteriously disappeared 
********************************************************************************************************/
-- declare variables
declare @message_text varchar(1024), -- actually nvarchar in msdb.dbo.sysjobhistory
 @step_name sysname,
 @job_name sysname,
 @subject_line varchar(100),
 @ec int,
 @rc int

-- set session
SET NOCOUNT ON

-- 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)
and message not like ('% succeeded%')
order by h.run_date desc, h.run_time desc

insert admin.dbo.agent_job_log (job_name, step_name, message)
values (@job_name, @step_name, @message_text)

if @notify_recipient_list is not null 
 begin
  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
  if object_id('sysmon.dbo.safe_sendmail') is not null
   exec @rc = sysmon.dbo.safe_sendmail @recipients = @notify_recipient_list, 
       @subject = @subject_line, 
       @message = @message_text
  else
   exec @rc = master.dbo.xp_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

RETURN




GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO