win2003+sql2005下使用使用sql2005自动发送邮件

在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,邮件帐户：[e-mail] 

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='[e-mail]',

@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。

 

 

如果要判断邮件是否最终发送成功可通过@mailitem_id到状态表中查询，如下

declare @mailitem_id int

exec msdb.dbo.sp_send_dbmail
--配置文件名称
@profile_name='mailConfig',
--发送地址，多个以,分隔
@recipients='[e-mail]',
--标题
@subject='ppp',
--内容
@body='sss',
@mailitem_id=@mailitem_id output

select * from sysmail_allitems where mailitem_id=@mailitem_id
select * from sysmail_mailitems where mailitem_id=@mailitem_id
select * from sysmail_event_log where mailitem_id=@mailitem_id

其中sysmail_event_log的event_type字段如果是success就是成功的，但不会马上返回