20200323-SQLServer邮件预警

问题：

OA 系统向异构系统推送待办时偶尔会出现各种原因导致推送失败，为了避免问题升级，需要管理员第一时间知道推送失败的情况，并手动进行干预。

问题分析：

待办推送状态会记录在OA 数据库wx_scanlog 表中，当resultstatus 状态为-1 时表示待办推送失败。

解决方案：

一：SQLServer 触发器
为了及时知道待办推送失败信息，可以使用SQLServer 触发器，当wx_scanlog 表中新增数据时，校验resultstatus 状态值，当值为-1 时，自动发送邮件到指定邮箱。

二：Grafana 查看待办状态
通过Grafana 连接OA 数据库，指定时间间隔查看待办状态，例如如下SQL ：

select top 10 scantime , content , resultcontent from wx_scanlog where resultstatus =-1  order by scantime desc ;

一：SQLServer 触发器

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms189635(v=sql.90)

过程如下：
下面开始配置 sql 发送电子邮件：
启用 sql server 邮件的功能

exec sp_configure 'show advanced options' ,1
go
reconfigure ;
go

配置选项 ‘show advanced options’ 已从 0 更改为 1 。请运行 RECONFIGURE 语句进行安装。

exec sp_configure 'Database Mail XPs' ,1
go
reconfigure ;
go

配置选项 ‘Database Mail XPs’ 已从 0 更改为 1 。请运行 RECONFIGURE 语句进行安装。
使用下面的语句查看数据库邮件功能是否开启成功和数据库配置信息：
查询数据库的配置信息

select * from sys . configurations

查看数据库邮件功能是否开启， value 值为 1 表示已开启， 0 为未开启

select name , value , description ,
       is_dynamic , is_advanced
from sys . configurations
where name like '%mail%'

---name value   description is_dynamic  is_advanced
---Database Mail XPs    1   Enable or disable Database Mail XPs 1   1

步骤二：

if exists( SELECT * FROM msdb .. sysmail_account WHERE NAME = 'chenmail' )
begin
    EXEC msdb .. sysmail_delete_account_sp @account_name = 'chenmail'
end
exec msdb .. sysmail_add_account_sp     -- 创建邮件账户
        @account_name = 'cjcamail'   -- 邮件帐户名称
       , @email_address = 'chenjuchao163@163.com'    -- 发件人邮件地址  
       , @display_name = 'chenjuchao'      -- 发件人姓名  
       , @replyto_address = null        -- 回复地址
       , @description = null          -- 邮件账户描述
       , @mailserver_name = 'smtp.163.com'    -- 邮件服务器地址
       , @mailserver_type = 'SMTP'        -- 邮件协议
       , @port = 25                  -- 邮件服务器端口  
       , @username = 'chenjuchao163'        -- 用户名  
       , @password = '**********'      -- 密码  
       , @use_default_credentials = 0    -- 是否使用默认凭证， 0 为否， 1 为是
       , @enable_ssl = 1        -- 是否启用 ssl 加密， 0 为否， 1 为是
       , @account_id = null  -- 输出参数，返回创建的邮件账户的 ID

– 步骤三：

if exists( SELECT * FROM msdb .. sysmail_profile where NAME = N'SendEmailProfile0323' ) -- 判断名为 SendEmailProfile0323 的邮件配置文件是否存在
begin  
    exec msdb .. sysmail_delete_profile_sp @profile_name = 'SendEmailProfile0323'  -- 删除名为 SendEmailProfile0323 的邮件配置文件
end
exec msdb .. sysmail_add_profile_sp     -- 添加邮件配置文件
     @profile_name = 'SendEmailProfile0323' ,   -- 配置文件名称    
     @description = ' 数据库发送邮件配置文件 ' ,     -- 配置文件描述      
     @profile_id = NULL        -- 输出参数，返回创建的邮件配置文件的 ID

步骤四：
– 邮件账户和邮件配置文件相关联

exec msdb .. sysmail_add_profileaccount_sp   
     @profile_name = 'SendEmailProfile0323' ,   -- 邮件配置文件名称     
     @account_name = 'chenmail' ,    -- 邮件账户名称       
     @sequence_number = 1    -- account 在 profile 中的顺序，一个配置文件可以有多个不同的邮件账户

到这里 sql 发送邮件的配置就基本结束了。下面创建一个触发器实现用户注册成功后，发送邮件给用户。

然后创建一个 insert 类型的 after 触发器：

create trigger undo_fail_cjc_tr
--alter trigger undo_fail_cjc_tr
on wx_scanlog
after insert
as
    declare @errormsg nvarchar (1000 )
    declare @resultcontent  nvarchar (500 )
    declare @content  nvarchar (500 )
    declare @title    nvarchar (100 )
    declare @xxx      nvarchar (1000 )
    declare @count    int
    declare @id       int
    select @count = COUNT (1 ) from inserted
    select @id = id from inserted
    select @resultcontent =( select CAST ( resultcontent as nvarchar (1000 )) from wx_scanlog where id = @id )
    select @content =( select CAST ( content as nvarchar (1000 )) from wx_scanlog where id = @id )
    set @xxx = 'content 标题如下： ' + @content + CHAR ( 13 )+ 'resultcontent 内容如下： ' + @resultcontent
    --select @msgcode=msgcode,@errormsg=errormsg  from inserted
    -- if(@count>0)
    if (( @count >0 ) and ( select resultstatus from inserted )= '-1' )
    begin
        set @title = 'OA( 正式系统 ) 待办推送 Eanar 失败，请及时处理！ '
        exec msdb . dbo . sp_send_dbmail @profile_name = 'SendEmailProfile0323' ,  -- 邮件配置文件名称
                   @recipients = 'cjc@xxx.com' ,        -- 邮件发送地址
                        @subject = 'OA( 正式系统 ) 待办推送 Eanar 失败，请及时处理！ ' ,   -- 邮件标题
                   @body = @xxx ,    -- 邮件内容
                   @body_format = 'text'  -- 邮件内容的类型， text 为文本，还可以设置为 html
    end
go

执行上面的语句之后，大概两三秒钟，就会收到邮件了（如果没有出现错误的话）。如果没有收到邮件可以使用下面的语句查看邮件发送情况。

use msdb
go

邮件发送情况，可以用来查看邮件是否发送成功

select * from sysmail_allitems

发送邮件的记录

select * from sysmail_mailitems

数据库邮件日志，可以用来查询是否报错

select * from sysmail_event_log

邮件：

二：Grafana 查看待办状态

欢迎关注我的微信公众号"IT小Chen"