17.1 SQL Server数据库邮件

 

SQL Server数据库邮件

 

目录

• SQL Server数据库邮件
  • 简介
  • 配置SQL Server数据库邮件
  • 使用数据库邮件发送邮件
    • 1）发送邮件示例
    • 2）发送带有查询结果的电子邮件

 

简介

数据库电子邮件可以从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';

 

分类: SQL Server

标签: SQL Server , SQL Server高级 , 数据库