SQLServer Job 邮件发送

SQLServer Job 邮件发送

为了方便查看定时任务执行是否成功，同时能够及时发现存储过程中抛出的异常，采用发送邮件的方式来提醒这些问题。

邮件设置

• 邮件服务器名称：smtp.qq.com
• 端口号：587
• 开启POP3/SMTP服务，产生第三方客户端授权码

说明

邮件采用QQ邮件，由于QQ邮件服务器要求安全链接（SSL），因此采用587端口，而不是25。具体设置如下：

步骤

1.进入QQ邮箱，找到设置>账户>POP3/IMAP/SMTP/Exchange/CardDAV/CalDAV服务

2.开启POP3/SMTP服务，点击“生成授权码”，这会让发送短信验证

3.保存生成的授权码，这是在第三方软件上用的密码

SQLServer数据库配置

• 数据库邮件配置
• SQLServer代理 警报系统配置
• 操作员配置
• Job配置

说明

通过图形界面配置邮件信息，其实通过数据库提供的存储过程配置是一样的，邮件所用到表在msdb库中，具体操作如下表：

序号	表名称
1	sysmail_account
2	sysmail_attachments
3	sysmail_attachments_transfer
4	sysmail_configuration
5	sysmail_log
6	sysmail_mailitems
7	sysmail_principalprofile
8	sysmail_profile
9	sysmail_profileaccount
10	sysmail_query_transfer
11	sysmail_send_retries
12	sysmail_server
13	sysmail_servertype

步骤

一、数据库邮件配置
1.管理>数据库邮件>邮件配置数据库邮件

2.进入配置向导，点击下一步

如果为第一次配置，就选择第一选项就行，在点击下一步时，会提示邮件没有开启，点击确定开启。

3.建立配置配件
配置文件可以建立多个，需要配置每个配置文件名，可以对配置文件进行说明，点击添加，添加SMTP账户


4.SMTP邮件账户配置完，点击确定

点击下一步，进入到安全性配置，将公共复选框勾住，作为默认配置

点击下一步

5.配置完成

二、SQLServer代理 警报系统配置
1.开启服务代理
2.SQLServer代理，右键属性>警报系统>邮件会话>启用邮件配置文件，选择邮件系统：数据库邮件，邮件配置文件：JobMonitorProfile(刚刚配置的)

3.点击确定
4.重启服务代理（一定要重启）

三、操作员配置
1.开启服务代理
2.SQLServer代理>操作员>右键新建操作员>常规

Job配置
1.开启服务代理
2.SQLServer代理>作业>右键新建作业>通知，勾选电子邮件，选择操作员，选择作业完成、失败、还是成功发送邮件

SQLServer 邮件代码

下面为发送邮件的SQL，存储过程异常数据通过begin try……end try begin catch……end catch 存储到T_SYS_ExceptionLog表中，然后通过定时检索，如果有异常就发送邮件，代码如下

create procedure [dbo].[proc_Sys_ExceptionSendEmail]
as
/********************************
--function:异常数据发送邮件
--author:zhujt
--create date:2016-12-2 10:25:41
*********************************/
begin
    declare 
        @profileName varchar(100),
        @Html varchar(max);

    set @Html='<table style="border:1px solid;"><tr><th>日志操作类型</th><th>日志标题</th><th>日志内容</th><th>文件路径</th><th>创建时间</th></tr>'+
    (select 
           'tds'+case ExceptionType 
              when 1 then '数据定时生成' 
              when 2 then '数据操作' 
           end +'tde', 
           'tds'+ExceptionTitle+'tde',
           'tds'+ExceptionMessage+'tde',
           'tds'+Module+'tde',
           'tds'+CONVERT(varchar(23),CreateDate,120)+'tde'
      from T_SYS_ExceptionLog
     where CreateDate>=CONVERT(varchar(10),DATEADD(DAY,-1,GETDATE()),120)+' 00:00:00'
    FOR XML PATH('tr'), ELEMENTS
    )
    +'</table>';

    select @profileName=name 
      from msdb.dbo.sysmail_profile x
     where exists(select 1 
                    from msdb.dbo.sysmail_principalprofile 
                   where profile_id=x.profile_id 
                     and is_default=0
                  );
    if @profileName is not null and LEN(@profileName)>0 and @Html is not null and LEN(@Html)>0
        begin     
            set @Html=REPLACE(REPLACE(@Html,'tds','<td>'),'tde','</td>');
            set @Html='<style>table{border-right:1px solid;border-bottom:1px solid;}td,th{border-left:1px solid;border-top:1px solid;}</style>'+@Html;

            exec msdb.dbo.sp_send_dbmail 
                @profile_name = @profileName,  
                @recipients = 'JobEmail@qq.com',
                @subject = 'JobEmail', 
                @body = @Html,
                @body_format = 'HTML';
        end
end