job alert sp

/*****************************************************************************
Created On 11/09/2006 To Update DB's JobInfo to
PerfAnaly..db_jobstatus and Send Alert Mails When The JobStatus Is Changed

Note: Specifying @job_name = '' or 'ALL' means to detect all jobs
         V080402
******************************************************************************/
CREATE PROCEDURE dbo.DB_JobAlert_sp
 (
  @job_name varchar(128) = '',
  @RecreateTbl_db_jobstatus bit = 0,
  @DB_IPAddress varchar(100) = '',
  @MailTo varchar(8000) = '',
  @MailCC varchar(8000) = '',
  @MailBcc varchar(8000) = ''
 )
AS

declare @jobname varchar(128), @last_run_outcome tinyint, @last_outcome_message nvarchar(1024), @message nvarchar(1024),
 @last_run_date int, @last_run_time int, @last_run_duration int, @jobstatus tinyint, @job_id varchar(36), @step_name varchar(128),
 @MailSubject varchar(200),@MailBody varchar(8000), @ToAddress varchar(255), @CcAddress varchar(255), @step_id int, @current_step_id int,
 @status_message varchar(10), @send_email bit, @Mail_To varchar(8000), @Mail_Cc varchar(8000), @Mail_Bcc varchar(8000), @enabled bit,
 @duration varchar(20), @du varchar(20), @run_status int, @step_retry bit, @step_status varchar(20), @timestamp varchar(20)
/* Begin Create Table db_jobstatus in DB */
if @RecreateTbl_db_jobstatus = 1
  begin
 if exists (select name from sysobjects where name='db_jobstatus'
   and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  drop table [db_jobstatus]
  end

if not exists (select name from sysobjects where name='db_jobstatus'
   and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  begin
 CREATE TABLE [dbo].[db_jobstatus] (
 [jobname] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [enabled] [bit] NOT NULL ,
 [jobstatus] [tinyint] NOT NULL ,
 [ToAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [CcAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [lasteditby] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [lasteditdt] [datetime] NULL
 ) ON [PRIMARY]

 ALTER TABLE [dbo].[db_jobstatus] ADD
 CONSTRAINT [DF_db_jobstatus_enabled] DEFAULT (1) FOR [enabled],
 CONSTRAINT [DF_db_jobstatus_jobstatus] DEFAULT (1) FOR [jobstatus],
 CONSTRAINT [DF_db_jobstatus_ToAddress] DEFAULT ('') FOR [ToAddress],
 CONSTRAINT [DF_db_jobstatus_CcAddress] DEFAULT ('') FOR [CcAddress],
 CONSTRAINT [PK_db_jobstatus] PRIMARY KEY  CLUSTERED
 (
  [jobname]
 )  ON [PRIMARY]
  end

/* Disable Non-existing Jobs  -- Modified To Send Mail If A Job Is Disabled or Removed*/
declare objs_cursor CURSOR LOCAL FAST_FORWARD for
 SELECT jobname, ToAddress, CcAddress
 from db_jobstatus (nolock) where jobname not in (
 select name from msdb..sysjobs (nolock) where enabled = 1)
 and enabled = 1
open objs_cursor
fetch next from objs_cursor
into @jobname, @ToAddress, @CcAddress

while @@fetch_status=0
begin
  set @Mail_To = @MailTo + ';' + @ToAddress
  set @Mail_Cc = @MailCc + ';' +@CcAddress
  set @Mail_Bcc = @MailBcc
  set @MailSubject = @@SERVERNAME + ' Job ['+ @jobname + '] Disabled or Removed'
  set @MailBody = @DB_IPAddress + char(10) +
    'Server Name : ' + @@SERVERNAME + char(10) +
    'Job Name : '+ @jobname + char(10) +
    'Job Status : ' + 'Disabled or Removed' + char(10)
  /* Send Email */
  exec master..SendMail_cdo_sp @From=@@SERVERNAME,@To=@Mail_To,@Cc=@Mail_Cc,@Bcc=@Mail_Bcc,
      @Subject=@MailSubject,@Body=@MailBody,@Attachment=''
  fetch next from objs_cursor
  into @jobname, @ToAddress, @CcAddress
end
close objs_cursor
deallocate objs_cursor

update db_jobstatus with (rowlock) set enabled = 0
where jobname not in (
select name from msdb..sysjobs (nolock) where enabled = 1)
and enabled = 1

/* Get All Tables Info */
declare objs_cursor CURSOR LOCAL FAST_FORWARD for
-- SELECT a.job_id, a.name, b.last_run_outcome, b.last_outcome_message, last_run_date, last_run_time, last_run_duration
-- FROM msdb.dbo.sysjobs a (nolock), msdb.dbo.sysjobservers b (nolock)      
-- WHERE a.job_id = b.job_id and a.enabled = 1 and (@job_name = '' or @job_name = 'ALL' or a.name = @job_name)

 /* Any Step In The Job Failed Will Be A Failure -- 04/02/2008 */
 SELECT a.job_id, a.name, min(c.run_status), b.last_outcome_message, b.last_run_date, b.last_run_time, b.last_run_duration
 FROM msdb.dbo.sysjobs a (nolock), msdb.dbo.sysjobservers b (nolock), msdb..sysjobhistory c (nolock)
 WHERE a.job_id = b.job_id and a.enabled = 1 and c.step_id > 0 and c.job_id =a.job_id and c.run_date >= b.last_run_date and c.run_time >= b.last_run_time
 group by a.job_id, a.name, b.last_outcome_message, b.last_run_date, b.last_run_time, b.last_run_duration

open objs_cursor
fetch next from objs_cursor
into @job_id, @jobname, @last_run_outcome, @last_outcome_message, @last_run_date, @last_run_time, @last_run_duration

while @@fetch_status=0
begin
 if @last_run_outcome = 0 set @status_message = 'Failed'
  else   set @status_message = 'Succeeded'
 set @send_email = 0
 if exists(select jobname from db_jobstatus (nolock) where jobname=@jobname)
   begin
  select @jobstatus = jobstatus, @ToAddress = ToAddress, @CcAddress = CcAddress, @enabled = enabled
   from db_jobstatus (nolock) where jobname=@jobname
  if @enabled <> 1
    begin
   set @send_email = 1
   set @status_message = 'Enabled'
   print 'Job ' + @jobname + ' ' + @status_message
    end
  if @jobstatus <> @last_run_outcome and @last_run_outcome = 0
    begin
   set @send_email = 1
   print 'Job ' + @jobname + ' ' + @status_message
    end
  if @jobstatus <> @last_run_outcome and @last_run_outcome = 1
    begin
   set @send_email = 1
   print 'Job ' + @jobname + ' ' + @status_message
    end
  update db_jobstatus with (rowlock) set
   enabled = 1,
   jobstatus = @last_run_outcome,
   lasteditby = @@SERVERNAME,
   lasteditdt = getdate()
  where jobname = @jobname
   end
 else
   begin
  if @last_run_outcome = 0
    begin
   set @send_email = 1
   print 'Job ' + @jobname + ' ' + @status_message
    end
  insert into db_jobstatus with (rowlock) (
   jobname,
   enabled,
   jobstatus,
   ToAddress,
   CcAddress,
   lasteditby,
   lasteditdt )
  values (@jobname,
   1, 
   @last_run_outcome,
   '',
   '',
   @@SERVERNAME,
   getdate() )
   end

 if @send_email = 1
   begin
  /* Duration Convertion Added on 11/30/2006 */
  set @du = reverse(convert(varchar(20),@last_run_duration))
  if len(@du) < 6 set @du = @du + replicate('0',6-len(@du))
  set @duration = ''
  while len(@du) > 2
    begin
   set @duration = @duration + left(@du,2) + ':'
   set @du = right(@du, len(@du)-2)
    end
  set @duration = reverse(@duration + @du)

  /* TimeStamp Convertion Added by Darrell 11/31/2006 */
  set @du = reverse(convert(varchar(20),@last_run_time))
  if len(@du) < 6 set @du = @du + replicate('0',6-len(@du))
  set @timestamp = ''
  while len(@du) > 2
    begin
   set @timestamp = @timestamp + left(@du,2) + ':'
   set @du = right(@du, len(@du)-2)
    end
  set @timestamp = reverse(@timestamp + @du)

  set @Mail_To = @MailTo + ';' + @ToAddress
  set @Mail_Cc = @MailCc + ';' +@CcAddress
  set @Mail_Bcc = @MailBcc
  set @MailSubject = @@SERVERNAME + ' Job ['+ @jobname + '] ' + @status_message
  set @MailBody = @DB_IPAddress + char(10) +
    'Server Name : ' + @@SERVERNAME + char(10) +
    'Job Name : '+ @jobname + char(10) +
    'Job Status : ' + @status_message + char(10) +
    'DateTime : ' + convert(varchar(10),@last_run_date) + ' ' + @timestamp + char(10) +
    'Duration : ' + @duration + char(10) +
    'Message : ' + @last_outcome_message + char(10)

  /* Get Step Messages -- Query Modified on 11/30/2006 */
  declare step_cursor CURSOR LOCAL FAST_FORWARD for
   select step_id, step_name, message, run_duration, run_status from msdb..sysjobhistory (nolock)

   where step_id > 0 and job_id = @job_id and run_date >= @last_run_date and run_time >= @last_run_time 
   order by instance_id
  open step_cursor
  fetch next from step_cursor
  into @step_id, @step_name, @message, @last_run_duration, @run_status
  set @current_step_id = 0
  set @step_retry = 0
  while @@fetch_status=0 and ( @current_step_id < @step_id  or  @step_retry = 1 )
    begin
   /* Step Status detection Added on 11/31/2006 */
   if @run_status = 2  set @step_retry = 1  else set @step_retry = 0
   set @step_status = case
    when @run_status = 0 then 'Failed'
    when @run_status = 1 then 'Successful'
    when @run_status = 2 then 'Failed, Will Retry'
    else 'Status ' + convert(varchar(20),@run_status)
    end
   /* Duration Convertion Added by Darrell 11/30/2006 */
   set @du = reverse(convert(varchar(20),@last_run_duration))
   if len(@du) < 6 set @du = @du + replicate('0',6-len(@du))
   set @duration = ''
   while len(@du) > 2
     begin
    set @duration = @duration + left(@du,2) + ':'
    set @du = right(@du, len(@du)-2)
     end
   set @duration = reverse(@duration + @du)
   set @MailBody = @MailBody + char(10) + 'Step ' + convert(char(3),@step_id) + ' ('+@duration + ') ['
     + @step_status + '] : ' + @step_name + char(10) + @message + char(10)
      set @current_step_id = @step_id
      fetch next from step_cursor
      into @step_id, @step_name, @message, @last_run_duration, @run_status
    end
  close step_cursor
  deallocate step_cursor
  /* Send Email */
  exec master..SendMail_cdo_sp @From=@@SERVERNAME,@To=@Mail_To,@Cc=@Mail_Cc,@Bcc=@Mail_Bcc,
      @Subject=@MailSubject,@Body=@MailBody,@Attachment=''
   end
  fetch next from objs_cursor
  into @job_id, @jobname, @last_run_outcome, @last_outcome_message, @last_run_date, @last_run_time, @last_run_duration
end
close objs_cursor
deallocate objs_cursor
GO

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/90986/viewspace-706112/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/90986/viewspace-706112/