如何用SQL Server来自动发送邮件

如何用SQL Server来自动发送邮件?

前提： SQL Server所在的主机必须能上网……
如果有什么意外发不出去，请先检查用 Foxmail 客户端软件能不能发出去。

准备一个允许 'SMTP' 邮件协议的邮箱， 163或者qq邮箱等都可以。
下面以qq为例。

在 SQL Server 中设置：
先在 msdb 库中开启 Broke 选项：
select d.is_broker_enabled from sys.databases d where d.name='msdb'
--ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE
ALTER DATABASE msdb SET ENABLE_BROKER
--1. 打开高级配置
EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'database mail xps',1
RECONFIGURE WITH OVERRIDE
GO
--2. 创建邮件帐户信息
DECLARE @account_id INT,@account_name NVARCHAR(128)
SET @account_name = 'ErrorMailAccount'
SELECT @account_id = account_id FROM msdb..sysmail_account WHERE NAME=@account_name
IF (@account_id IS NOT NULL)
BEGIN
EXEC msdb..sysmail_delete_account_sp @account_id, @account_name
END

EXEC msdb..sysmail_add_account_sp
@ACCOUNT_NAME = @account_name, -- 邮件帐户名称
@EMAIL_ADDRESS = '609826027@qq.com', -- 发件人邮件地址
@DISPLAY_NAME = 'Fkxt系统管理员', -- 发件人姓名
@REPLYTO_ADDRESS = NULL,
@DESCRIPTION = NULL,
@MAILSERVER_NAME = 'smtp.qq.com', -- 邮件服务器地址
@MAILSERVER_TYPE = 'SMTP', -- 邮件协议
@PORT = 25, -- 邮件服务器端口
@USERNAME = '609826027@qq.com', -- 用户名
@PASSWORD = ' ', -- 密码
@USE_DEFAULT_CREDENTIALS = 0,
@ENABLE_SSL = 0,
@ACCOUNT_ID = NULL
GO
--3.数据库配置文件
IF EXISTS(
SELECT 1
FROM msdb..sysmail_profile
WHERE NAME = N'ErrorEmailProfile'
)
BEGIN
PRINT '存在，先删除'
EXEC msdb..sysmail_delete_profile_sp @profile_name = 'ErrorEmailProfile'
END
EXEC msdb..sysmail_add_profile_sp
@profile_name = 'ErrorEmailProfile', -- profile 名称
@description = '数据库邮件配置文件', -- profile 描述
@profile_id = NULL
GO
--4.用户和邮件配置文件相关联
EXEC msdb..sysmail_add_profileaccount_sp
@profile_name = 'ErrorEmailProfile', -- profile 名称
@account_name = 'ErrorMailAccount', -- account 名称
@sequence_number = 1 -- account 在 profile 中顺序

--5.发送文本测试邮件
EXEC msdb..sp_send_dbmail
@profile_name = 'ErrorEmailProfile',
@recipients = '609826027@qq.com', --收件人
@subject = 'Test title this is test ',
@body = N'z中文邮件内容中文邮件内容'
GO

其它相关脚本：

--查看账号
select * FROM msdb..sysmail_account
--查看配置文件
select * from msdb..[sysmail_profile]
--查看是否已启动数据库邮件激活
EXEC msdb.dbo.sysmail_help_status_sp;
--启动数据库邮件激活
EXEC msdb.dbo.sysmail_start_sp;
--检查邮件队列的状态
--如果邮件队列的状态不正常，
--请使用 sysmail_stop_sp 尝试停止队列，然后再使用 sysmail_start_sp 启动队列。
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

--查看最近的邮件发送情况
select top 5 m.mailitem_id
,m.profile_id
,p.name as profileName
,g.description AS [log_desc]--邮件发送有异常，这里会有显示
,m.sent_status
,CASE sent_status when 1 then 'Successed' else 'Failed' END AS sent_status_desc
,m.subject
,m.body
,m.body_format
,m.send_request_date
from msdb..[sysmail_mailitems] AS m
left join msdb..sysmail_profile AS p on m.profile_id=p.profile_id
left join msdb..sysmail_log g on m.mailitem_id = g.mailitem_id
order by mailitem_id desc

--清除30天前的邮件和日志
DECLARE @DeleteBeforeDate DateTime
SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE())
EXEC msdb..sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate
EXEC msdb..sysmail_delete_log_sp @logged_before = @DeleteBeforeDate