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';
posted @   平元兄  阅读(312)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
点击右上角即可分享
微信分享提示