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 = '[email protected]',
@replyto_address = '[email protected]',
@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)发送邮件示例
比如向邮件地址[email protected]
发送邮件:
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@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;
最后,把邮件发送到[email protected]
:
EXEC msdb.dbo.sp_send_dbmail
@recipients='[email protected]',
@subject = 'Inventory List',
@body = @tableHTML,
@body_format = 'HTML';
标签:product,name,数据库,Server,17.1,SQL,电子邮件,id,邮件
From: https://www.cnblogs.com/michaelshen/p/17077324.html