-- 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