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"，共同学习，共同成长！！！