下面的脚本演示，在 SQL Server 2005/2008 中，使用 Gmail 帐号配置数据库邮件功能
-- =================================================
-- 启用数据库邮件功能
-- =================================================
EXEC sys.sp_configure N'show advanced options', 1
RECONFIGURE;
EXEC sys.sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
-- =================================================
-- 配置
-- =================================================
DECLARE
@account_name sysname,
@profile_name sysname;
SELECT
@account_name = N'GmailTest@gmail.com', -- 数据库邮件帐号
@profile_name = N'gmail'; -- 数据库邮件配置文件名
-- 数据库邮件帐号
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@email_address = N'GmailTest@gmail.com', -- Email 地址
@display_name = N'gmail test', -- 回复地址
@mailserver_name = N'smtp.gmail.com', -- Gmail smtp 服务器地址
@port = 25, -- 端口号(对于Gmail, 如果 25 不通则改用 456)
@username = N'GmailTest@gmail.com', -- Gmail 邮件地址
@password = N'abc.123', -- Gmail 邮件帐号密码
@use_default_credentials = 0,
@enable_ssl = 1; -- 启用SSL 通讯加密
-- 数据库邮件配置文件
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name;
-- 把数据库邮件帐号添加到邮件配置文件中
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1;
-- 授予任意用户使用数据库邮件配置文件的权限
EXEC msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = N'guest',
@profile_name = @profile_name,
@is_default = 0;
-- =================================================
-- 发送邮件
-- =================================================
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name,
@recipients = N'GmailTest@gmail.com', -- 收件人地址
@subject = N'test mail from database', -- 邮件主题
@body = N'this is a test mail'; -- 邮件内容
--6. 查看邮件发送情况：
use msdb
go
select * from sysmail_allitems
select * from sysmail_mailitems
select * from sysmail_event_log
-- =================================================
-- 删除配置
-- =================================================
/* --
DECLARE
@account_name sysname,
@profile_name sysname;
SELECT
@account_name = N'GmailTest@gmail.com',
@profile_name = N'gmail';
-- 从邮件配置文件中删除数据库邮件帐号
EXEC msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name;
-- 删除数据库邮件帐号
EXEC msdb.dbo.sysmail_delete_account_sp
@account_name = @account_name;
-- 删除数据库邮件配置文件
EXEC msdb.dbo.sysmail_delete_profile_sp
@profile_name = @profile_name;
-- 禁用数据库邮件功能
EXEC sys.sp_configure 'Database Mail XPs', 0;
RECONFIGURE;
EXEC sys.sp_configure N'show advanced options', 0
RECONFIGURE;
-- */
'
-- =================================================
-- 发送附件
-- =================================================
通过电子邮件发送文件附件的能力对工作效率来说是十分重要的。下面的脚本将会发送一封电子邮件，其中包含文件名为FileAttachment.txt的附件，给附件存储于我的C盘驱动器上。
你需要确保文件的存在，这样发送过程就不会发生错误。
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body='Message Body',
@subject ='Message Subject',
@profile_name ='Database-mailProfile',
@file_attachments ='C://FileAttachment.txt';
-- =================================================
-- 敏感度和重要性
-- =================================================
通常需要将一封邮件标记为敏感或重要，以使得收件人知道这些信息需要小心保护。下面的脚本将会发送一封邮件，敏感度为私人，重要性为高级。此外，脚本将会复制邮件地址yourname@yourdomain.com到信件中。
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body='Message Body',
@sensitivity ='Personal',
@importance ='High',
@copy_recipients ='chapman.tim@gmail.com',
@subject ='Message Subject',
@profile_name ='Database-mailProfile';
-- =================================================
-- 查询结果
-- =================================================
拥有电子邮件发送能力的数据库引擎的一个重要特征是它可以让你向发送过程传递一个查询。在SQL Mail和Database Mail中都有该特性。Database Mail中的一个新特性是可以将查询结果作为附件，这一点在下面的脚本中展示。如果你不将查询结果作为一文件，那么它将被放置在邮件正文中。见列表B。
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body='Message Body',
@subject ='Message Subject',
@profile_name ='DatabaseMailProfile',
@query ='SELECT Product FROM sb2..SalesHistory GROUP BY Product HAVING COUNT(*) > 3',
@attach_query_result_as_file = 1,
@query_attachment_filename ='Results.txt'
HTML格式的信件
在数据库邮件中我最喜爱的新特征是可以将你的邮件格式化为HTML格式。简单看来，这没什么大不了的，但是这确实带来一些很好的可能性。在前面的例子中，你发送的查询结果包含在邮件正文中，因为该例子只包含一列数据，所以看起来没有什么不方便的。
但是，当在查询结果集中包含多个域时，格式化变得每况愈下。一个好消息是，可以很简单的使用SQL Server 2005中的一些XML和HTML新特征来格式化你的查询结果集，因此，可以很容易的在信件中查看查询结果。列表C中的脚本演示了如何利用查询结果发送一封XML/HTML格式的电子邮件。
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT Product AS 'td','',SUM(SalePrice) AS 'td'
FROM SalesHistory GROUP BY Product FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1>Sales Reports</H1><body bgcolor=yellow><table border = 2><tr><th>Product</th><th>SaleAmount</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'chapman.tim@gmail.com',
@body = @body,
@body_format ='HTML',
@subject ='Message Subject',
@profile_name ='DatabaseMailProfile'
当你发送HTML格式的邮件时，允许你将查询结果嵌入到邮件的查询部分，从而可以将查询结果格式化成为更容易看的形式。（虽然，你可能会争论说我在信件中使用的黄色背景，看起来并不是很舒服。
在SQL Server以前的版本中，可以通过SQL Mail来实现对电子邮件的收发等功能，必须在SQL Server服务器上安装支持扩展 MAPI 的电子邮件应用程序（例如：microsoft office outlook等）。而数据库邮件则是Microsoft SQL Server 2005 Database Engine与SMTP结合来发送电子邮件的企业解决方案。通过使用数据库邮件，数据库应用程序可以向用户发送电子邮件(包括HTML消息、查询结果和附件），并不需要MAPI的支持。
配置数据库邮件之前，需满足一下几个条件：
1）数据库邮件必须启用。出于安全性的考虑，默认地是不开启数据库邮件功能。启用数据库邮件功能有三种方式：使用SQL Server 外围应用配置器工具、“数据库邮件配置向导”或者sp_configure存储过程。
这里先介绍第一种：打开SQL Server“配置工具”中的“外围应用配置器”，选择“功能的外围应用配置器”。在弹出的对话框中选择数据库邮件，并选择“启用数据库邮件存储过程”，如图：
2）启用数据库邮件主机数据库上的service broker。默认的数据库主机数据库是msdb,其service broker默认是启用的。您可以通过如下脚本来查看msdb是否启用了Service Broker：
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
如果未启用，则返回“0”，如果启用，则返回1.你可以通过下面脚本来启用Service Broker：
ALTER DATABASE msdb SET ENABLE_BROKER
3）数据库邮件外部可执行程序（DatabaseMail90.exe,位于SQL server安装路径中的MSSQL/Binn目录）需要访问SMTP服务器。
在开始配置数据库邮件之前，让我们先大致了解一下数据库邮件的体系结构。数据库邮件有4个组件：配置文件、邮件处理组件、可执行文件以及“日志记录和审核组件”。
配置组件包括:
1）数据库邮件帐户包含诸如SMTP服务器名、身份验证类型和电子邮件地址等。
2）数据库邮件配置文件是数据库邮件帐户的集合。
邮件处理组件
主要的数据库邮件组件就是刚才所说的数据库邮件主机数据库，默认是msdb。
数据库邮件可执行文件，数据库邮件使用一个外部可执行文件来处理邮件，降低了对SQL Server的影响。当有邮件要处理时，数据库邮件使用Service Broker激活外部程序(DataMail90.exe)发送邮件。
日志记录和审核组件，可以通过数据库邮件日志或查询sysmail_log系统视图来查看相关日志记录。
现在就让我们开始配置我们的数据库邮件吧，首先简单介绍一下环境：
操作系统：windows server2003 R2
数据库： SQL Server 2005 SP2
SMTP邮件服务器：smtp.itproctiy.net,邮件帐户：sql@itprocity.net
1)打开SSMS（SQL Server Management Studio)，在对象浏览器中依次展开服务器、管理节点，在数据库邮件上点击右键，如图：
2）选择“配置数据库邮件”，将出现“数据库邮件配置向导”对话框。如图：
3）点击“下一步”，如果数据库邮件尚未启动，则弹出一个对话框，询问是否启用数据库邮件，选择“是”。
4）在“配置文件名”文本框中输入“ITProCityProfile”，并单击“添加”以加入一个SMTP帐户。
5）在“新建数据库邮件帐户”对话框中，输入相关信息，确定。注意您的SMTP是否支持匿名。
6）点击下一步，在“管理配置文件安全性”页面上将“ITProCityProfile”配置为公共配置文件，并单击下一步。
7）这时将出现“配置参数”页面，我们保持默认，单击下一步，再单击完成。
到此一个数据库邮件配置过程就结束了，我们怎么测试我们的数据库邮件是否正常呢？
1）再次右键对象浏览器中的“数据库邮件”节点，选择“发送测试电子邮件”，出现如下：
2）在数据配置文件的下拉框中选择我们刚才创建的“ITProCityProfile”，填写收件人地址，点击“发送……”出现下图：（注意：发送电子邮件后面的数字"16"是测试邮件的ID，可以通过此唯一ID在日志中查询该邮件的状态。）
或者您可以通过如下脚本来发送一封测试邮件：
exec msdb.dbo.sp_send_dbmail
@profile_name='ITProCityProfile',
@recipients='webmaster@itprocity.net',
@body='这是一封测试邮件',
@subject='测试'
那么接收一下吧！
为什么我还没有收到测试邮件啊？？
您可以通过以下脚本来查看邮件的状态：
SELECT * FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id = 16 /*测试邮件的ID*/
在返回的数据中查看“sent_status"字段，来确认是否发出。
您也可以通过右键“数据库邮件”，选择“查看数据库邮件日志”来了解邮件的状态。
其中常见的不能发送的错误消息：
1）ExternalMailQueue 接收到无效的 XML 消息格式。conversation_handle……
解决：至少为SQL Server安装SP1
2)由于邮件服务器故障，无法将邮件发送给收件人。 (使用帐户 1 (2007-12-06T10:08:32) 发送邮件。 异常邮件: 无法将邮件发送到邮件服务器。 (不允许使用邮箱名称。 服务器响应为: You are not authorized to send mail, authentication is required)。
解决：你的SMTP邮件服务器要求认证，而你没有提供正确的帐户密码或者是错误地选择了匿名身份认证。
3)使用帐户 1 (2007-12-06T11:17:08) 发送邮件。 异常邮件: 无法将邮件发送到邮件服务器。 (不是本地用户；请尝试不同的路径。服务器响应为: auth error. (è??¤óà??꧰ü))。
解决：部分免费的公共邮箱的SMTP服务器不允许此项服务，请选择其他的SMTP。
9912
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
