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