DatabaseMail发送job执行日志

--1.开启数据库高级属性

exec sp_configure 'show advanced options',1

reconfigure

go

exec sp_configure 'database mail xps',1

reconfigure

go

 

--2.创建邮件帐户信息

if exists(select name from msdb..sysmail_account where name =N'ETLErrorMailLog')

begin

exec msdb..sysmail_delete_account_sp @account_name='ETLErrorMailLog'

end

go

EXEC msdb..sysmail_add_account_sp      

@ACCOUNT_NAME = 'ETLErrorMailLog',       -- 邮件帐户名称      

@EMAIL_ADDRESS = 'emmy_lee@126.com',     -- 发件人邮件地址      

@DISPLAY_NAME = '系统管理员',            -- 发件人姓名      

@REPLYTO_ADDRESS = NULL,   

@DESCRIPTION = NULL,      

@MAILSERVER_NAME = 'SMTP.126.COM',       -- 邮件服务器地址      

@MAILSERVER_TYPE = 'SMTP',               -- 邮件协议      

@PORT = 25,                              -- 邮件服务器端口      

@USERNAME = 'emmy_lee@126.com',          -- 用户名     

@PASSWORD = 'XXXXXXXXXXX',               -- 密码     

@USE_DEFAULT_CREDENTIALS = 0,      

@ENABLE_SSL = 0,      

@ACCOUNT_ID = NULL

GO

--3.数据库配置文件

if exists(select name from msdb..sysmail_profile where name=N'ETLErrorProfileLog')

begin

exec msdb..sysmail_delete_profile_sp @profile_name='ETLErrorProfileLog'

end

exec msdb..sysmail_add_profile_sp @profile_name = 'ETLErrorProfileLog',   -- profile 名称                                 

@description = '数据库邮件配置文件',                                  -- profile 描述 

@profile_id = null

go

--4.用户和邮件配置文件相关联

exec msdb..sysmail_add_profileaccount_sp @profile_name = 'ETLErrorProfileLog',  -- profile 名称                                         

@account_name    = 'ETLErrorMailLog',                                       -- account 名称                                         

@sequence_number = 1                                                        -- account 在 profile 中顺序

go

 

--5.发送文本测试邮件

exec msdb..sp_send_dbmail @profile_name='ETLErrorProfileLog'

,@recipients='lxwcel@126.com' --收件人

,@subject='aa'

,@body='aa'

go

 

--6.发送job日志邮件(如果要定时，在建个job跑下面的语句就行。)

declare @sqlQuery nvarchar(max)

set @sqlQuery=

 N'<H1 align="center">Job日志列表</H1>' +

 N'<table border="1" cellspacing="0" cellPadding="5" style="line-height:25px; font-size:12px;">' +

 N'<tr style="background:#e1e1e1;"><th nowrap>作业名称</th><th nowrap>步骤名称</th>' +

 N'<th nowrap>运行时间</th><th nowrap>持续时间</th><th nowrap>状态</th><th nowrap>日志详情</th>' +

 N'</tr>' +

 CAST((SELECT td=jobs.name,'',

              td=history.step_name,'',

              td=(left(left(run_date,4)+'-'+right(run_date,4),7)+'-'+right(run_date,2)),'',

              td=(left(left(left('000000',6-len(run_duration))+ltrim(run_duration),2)+':'+right(left('000000',6-len(run_duration))+ltrim(run_duration),4),5)+':'+right(left('000000',6-len(run_duration))+ltrim(run_duration),2)) ,'',

              td=(case run_status when 0 then '失败' when 1 then '成功' when 2 then '重试' when 3 then '已取消' when 4 then '正在进行中' end),'',

              td=history.message ,''

FROM msdb.dbo.sysjobhistory history INNER JOIN msdb.dbo.sysjobs jobs ON history.job_id=jobs.job_id

WHERE jobs.name = 'myjob' AND step_id <> 0 AND run_date = cast(replace(convert(date,getdate()),'-','') as int)

for xml path('tr'),type) as nvarchar(max))+

N'</table>' ;

exec msdb..sp_send_dbmail @profile_name=ETLErrorProfileLog,

@recipients='lxwcel@126.com',--收件人

@subject='job执行日志',

@body=@sqlQuery,

@body_format='html'

go

转载于:https://www.cnblogs.com/tianshansoft/archive/2012/02/17/2355362.html