17.1 SQL Server数据库邮件
SQL Server数据库邮件
简介
数据库电子邮件可以从SQL Server数据库引擎向用户发送电子邮件。电子邮件可以是纯文本或HTML，并可能包含附件。
数据库邮件可靠、可扩展、安全且可支持性好。
可靠性
- 数据库邮件使用SMTP发送电子邮件。
- SQL Server使用单独的过程来传递电子邮件，以最大程度地减少对服务器的性能影响。即使SMTP服务器脱机，SQL Server也会排队电子邮件。等SMTP服务器在线时将发送电子邮件。
- 数据库邮件可以使用多个SMTP服务器。如果SMTP服务器不可用，它将使用第二个SMPT服务器发送电子邮件。
可扩展
数据库邮件在后台异步发送电子邮件。使用sp_send_dbmail存储过程发送电子邮件。此存储过程将请求添加到ServiceBroker队列并立即返回。外部电子邮件组件接收请求并传递电子邮件消息。
安全
- 默认情况下，数据库邮件服务处于关闭状态。要发送电子邮件，必须先启用它。
- 用户必须是
msdb数据库中DatabaseMailUserRole数据库角色的成员才能发送电子邮件。 数据库邮件允许你保护邮件配置文件。- 数据库邮件可以配置附件的大小限制和附件的扩展名。如果要将文件从文件夹附加到电子邮件，SQL Server引擎帐户需要具有访问该文件的权限。
可支持性
- 日志记录–数据库邮件将电子邮件活动记录到
msdb系统数据库和Microsoft Windows应用程序事件日志中的表中。 - 审核–数据库邮件将邮件和附件的副本保存在
msdb数据库中。 - 多种电子邮件格式–数据库邮件支持纯文本和HTML格式。
配置SQL Server数据库邮件
首先，将Show Advanced设置更改为1:
sp_configure 'Show Advanced', 1;
reconfigure;
执行此操作后，就可以使用sp_configure存储过程查看当前服务器的所有全局配置设置：
sp_configure
然后，为当前SQL Server实例启用数据库邮件：
sp_configure 'Database Mail XPs', 1;
reconfigure;
第三不，使用msdb.dbo.sysmail_add_account_sp存储过程创建数据库邮件帐户：
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Primary Account',
@description = 'Account used by all mail profiles.',
@email_address = 'alert@qq.com',
@replyto_address = 'alert@qq.com',
@display_name = 'Database Mail',
@mailserver_name = 'smtp.qq.com';
第四步，创建数据库邮件配置：
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Public Profile',
@description = 'public profile for all users';
第五，将帐户添加到Public Profile：
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Public Profile',
@account_name = 'Primary Account',
@sequence_number = 1;
第六步，授予所有msdb数据库用户访问配置的权限：
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'public Profile',
@principal_name = 'public',
@is_default = 1;
使用数据库邮件发送邮件
使用msdb.dbo.sp_send_dbmail存储过程发送电子邮件。
1）发送邮件示例
比如向邮件地址report@qq.com发送邮件:
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'report@qq.com',
@body = 'This is a test message',
@subject = 'Database Mail Test';
2）发送带有查询结果的电子邮件
首先，查询产品id 1和2的库存：
SELECT
store_name,
product_name,
SUM(quantity)
FROM sales.stores s
INNER JOIN production.stocks i
ON i.store_id = s.store_id
INNER JOIN production.products p
ON p.product_id = i.product_id
WHERE p.product_id IN (1, 2)
GROUP BY store_name,product_name;
然后，将查询结果放到HTML表格中。结果集中的每一行都是带有＜tr＞标记的HTML表行：
SELECT
CAST((SELECT
td = store_name,
'',
td = product_name,
'',
td = SUM(quantity),
''
FROM sales.stores s
INNER JOIN production.stocks i
ON i.store_id = s.store_id
INNER JOIN production.products p
ON p.product_id = i.product_id
WHERE p.product_id IN (1, 2)
GROUP BY store_name,product_name
FOR xml PATH ('tr'), TYPE)
AS nvarchar(max));
第三步，将HTML表体（table body）转换为文本：
DECLARE @tableHTML NVARCHAR(MAX);
SET @tableHTML =
N'<h1>Inventory Report</h1>' +
N'<table border="1">' +
N'<tr><thead><th>Store Name</th><th>Product</th><th>Total Quantity</th></thead><tbody>' +
CAST ( (
SELECT
td=store_name,'',
td=product_name,'',
td=SUM(quantity),''
FROM sales.stores s
INNER JOIN production.stocks i
ON i.store_id = s.store_id
INNER JOIN production.products p
ON p.product_id = i.product_id
WHERE p.product_id IN (1, 2)
GROUP BY store_name,
product_name
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</tbody></table>' ;
SELECT @tableHTML;
最后，把邮件发送到report@qq.com:
EXEC msdb.dbo.sp_send_dbmail
@recipients='report@qq.com',
@subject = 'Inventory List',
@body = @tableHTML,
@body_format = 'HTML';
