把JOB的执行失败的情况以MAIL的形式通知用户--sp_send_dbmail

新建一个JOB，执行下面的存储过程就可以将当前服务器上执行出错的详情发送出来：

Job information notification as following.

USE [DBNAME] GO /****** Object:  StoredProcedure [dbo].[Send_Job_Failed_Email]    Script Date: 2016/5/5 10:31:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

CREATE PROCEDURE [dbo].[Send_Job_Failed_Email] AS BEGIN --每天提取失败Job发送邮件 by sunney. declare @int_count int declare @sublot_no varchar(30),        @lot_no varchar(30),        @prod_order_no varchar(30),        @status varchar(10),        @creation_date varchar(20)   Declare @profile_name varchar(20),        @rec_address  varchar(500)   DECLARE @Message nvarchar(max) DECLARE @Subject nvarchar(100)   Set @rec_address=N‘sidney.thong@serialsystem.com;waihon.cheong@serialsystem.com;chusoon.tan@serialsystem.com;grace.li@serialsystem.com;itcenter.pg@serialsystem.com‘ Set @profile_name=N‘b2b2‘   declare  @tb_mr table ( name varchar(500), step_id varchar(50), step_name varchar(500), run_date varchar(50), run_time varchar(50), body varchar(4000) ) insert into @tb_mr select  b.name,step_id,step_name,  substring(convert(varchar,run_date),1,4)  +‘-‘+ substring(convert(varchar,run_date),5,2)  +‘-‘+substring(convert(varchar,run_date),7,2) as run_date, substring(right(‘00000‘+ convert(varchar,run_time),6),1,2) + ‘:‘ + substring(right (‘00000‘+ convert(varchar,run_time),6),3,2) +‘:‘+substring(right (‘00000‘+ convert(varchar,run_time),6),3,2) as run_time, a.message  body from msdb.dbo.sysjobhistory a ,msdb.dbo.sysjobs b where a.job_id=b.job_id and name not in(‘syspolicy_purge_history‘,  ‘SSL_CRM_Forecast_Total‘,  ‘SSL_SSIS_ZSD030_PreSettlementCheck_Realtime‘,  ‘SSL_SSIS_SoBacklogAlert_Report‘,  ‘SSL_EnterpriseSearchEngine‘,  ‘SSL_B2B_Insert_SOR_AfterAllFromTemp‘) and a.step_id>0 and run_date=CONVERT(int, CONVERT(char(8), GETDATE(), 112)) and run_status=0 order by run_time desc   select @int_count=count(*) from @tb_mr   print  @int_count if @int_count>0 begin

  Select @subject =‘Job information notification for [Date: ]‘+Convert(varchar(20),getdate(),120)      SET @Message= N‘<H1>SQLNET System Job information</H1>‘ +       N‘<table border="1">‘ +       N‘<tr><th>name</th><th>step_id</th><th>step_name</th><th>run_date</th><th>run_time</th><th>Body</th>‘+       CAST ( (  Select td =name,‘‘,                      td =step_id,‘‘,                      td =step_name,‘‘,                      td =run_date,‘‘,                      td =run_time,‘‘,                      td =Body,‘‘               from @tb_mr     FOR XML PATH(‘tr‘), TYPE     ) AS NVARCHAR(MAX) ) +     N‘</table>‘ ;         EXEC msdb.dbo.sp_send_dbmail       @profile_name = @profile_name,       @recipients = @rec_address,       @subject = @Subject,          @body = @Message,       @body_format = ‘html‘; end END

把JOB的执行失败的情况以MAIL的形式通知用户--sp_send_dbmail

标签：