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