首页 > 代码库 > 在 SQL Server 2005 中配置数据库邮件
在 SQL Server 2005 中配置数据库邮件
一、 SQL Server发邮件原理和组件介绍：
数据库邮件有4个组件：配置文件、邮件处理组件、可执行文件以及“日志记录和审核组件”。
l 配置组件包括:
1）数据库邮件帐户包含诸如SMTP服务器名、身份验证类型和电子邮件地址等。
2）数据库邮件配置文件是数据库邮件帐户的集合。
l 邮件处理组件
要的数据库邮件组件就是刚才所说的数据库邮件主机数据库，默认是msdb。
l 数据库邮件可执行文件
数据库邮件使用一个外部可执行文件来处理邮件，降低了对SQL Server的影响。当有邮件要处理时，数据库邮件使用Service Broker激活外部程序(DataMail90.exe)发送邮件。
l 日志记录和审核组件
可以通过数据库邮件日志或查询sysmail_log系统视图来查看相关日志记录。
跟我们发送邮件一样，需要用户名和密码通过 SMTP（Simple Message Transfer Protocol） 去连接邮件服务器。我们想让SQL Server 来发送邮件，首先要告诉它用户名称，密码， 服务器地址，网络传送协议，邮件服务器的端口。。。等信息
二、 启用 SQL Server 2005 邮件功能
方法1：打开Sql Server外围应用配置器：
点击【功能外围应用配置】，在打开的页面中选择“数据库邮件”，选中“启用数据库邮件存储过程”
方法2：执行启用语句
use master
go
exec sp_configure ‘show advanced options‘,1
go
reconfigure
go
exec sp_configure ‘Database mail XPs‘,1
go
reconfigure
go
三、 启用数据库邮件主机数据库上的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
四、 配置数据库邮件
1)打开SSMS（SQL Server Management Studio)，在对象浏览器中依次展开服务器、管理节点，在数据库邮件上点击右键，如图：
2）选择“配置数据库邮件”，将出现“数据库邮件配置向导”对话框。如图：
3）点击“下一步”，如果数据库邮件尚未启动，则弹出一个对话框，询问是否启用数据库邮件，选择“是”。
4）在“配置文件名”文本框中输入“worklogmial”，并单击“添加”以加入一个SMTP帐户。
5）在“新建数据库邮件帐户”对话框中，输入相关信息，确定。
6）点击下一步，在“管理配置文件安全性”页面上将“worklogmial”配置为公共配置文件，并单击下一步。
7）这时将出现“配置参数”页面，我们保持默认，单击下一步，再单击完成。
五、 发送测试邮件
1）再次右键对象浏览器中的“数据库邮件”节点，选择“发送测试电子邮件”，出现如下：
2）在数据配置文件的下拉框中选择我们刚才创建的“worklogmail”，填写收件人地址(如：jsyhello@gmail.com)，点击“发送……”出现下图：（注意：发送电子邮件后面的数字"16"是测试邮件的ID，可以通过此唯一ID在日志中查询该邮件的状态。）
或者您可以通过如下脚本来发送一封测试邮件：
exec msdb.dbo.sp_send_dbmail
@profile_name=‘ worklogmail ‘,
@recipients=‘jsyhello@gmail.com‘,
@body=‘这是一封测试邮件‘,
@subject=‘测试‘
六、 常见问题
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。
七、 建立发送邮件的存储过程
--发送邮件模板存储过程
Create PROCEDURE [dbo].[MyMail]
@mailto varchar(max),
@mailsubject nvarchar(255),
@mailbody nvarchar(max)
AS
BEGIN
set @mailbody=replace(replace(@mailbody,‘<‘,‘<‘),‘>‘,‘>‘)
+N‘<br/>谢谢!<br/>祝您工作愉快!<br/>发件人:研发管理平台‘
exec msdb.dbo.sp_send_dbmail
@profile_name = ‘worklogmail‘,
@recipients = @mailto,
@subject = @mailsubject,
@body = @mailbody,
@body_format = ‘HTML‘
END
--日志提醒存储过程
Create proc [dbo].[MailUnWirte]
@days int=2
as
if EXISTS(select * from WorkWeek where datediff(day,WorkDay,getdate())=0 and Iflag=1)
begin
declare @calcDate datetime
select @calcDate=max(workday) from WorkWeek where datediff(day,WorkDay,getdate())>@days and Iflag=1
declare @username varchar(50)
declare @mailto varchar(max)
declare @depid varchar(20)
declare @mailbody nvarchar(max)
declare mailto cursor for
select S_User.Name,S_User.EMail,S_user.DepID
from S_User
inner join s_UserTransfer ut on ut.userid=S_User.id and ut.StartDate<=@calcDate
left join ProjectWorkLog l on l.EffortState=1 and S_User.id=l.worker and l.workday<=@calcDate
where S_User.Iflag=‘1‘ and S_User.IsEffort=1
group by S_User.Name,S_User.EMail,S_user.DepID
having isnull(sum(l.Effort),0)<8
- open mailto
fetch next from mailto into @username,@mailto,@depid
while @@fetch_status=0
begin
set @mailbody=@username+‘您好:<br/> 您截至到‘+convert(varchar(10),@calcDate,20)+‘，尚有工作日志未填写完毕,请尽快填写‘
exec MyMail @mailto,N‘日志未填写提醒‘,@mailbody
select @mailto=isnull(email,‘‘) from s_user inner join s_dep on s_dep.Manager=s_user.id where s_dep.id=depid
if @mailto!=‘‘
begin
set @mailbody=‘您好:<br/> ‘+@username+‘截至到‘+convert(varchar(10),@calcDate,20)+‘，尚有工作日志未填写完毕,请督促其按时填写‘
exec MyMail @mailto,N‘日志未填写提醒‘,@mailbody
end
fetch next from mailto into @username,@mailto,@depid
end
close mailto
deallocate mailto
end
八、 配置计划任务
1、新建作业
2、新建作业-常规：
步骤：常规设置完成，点击【步骤】选项，点击【新建】按钮进入
点击【确认】返回，可以看到步骤中增加了一条。
计划：步骤设置完成，点击【计划】选项，点击【新建】按钮进入
点击【确认】返回。可以看到计划中增加了一条。
其它选项可以不设置。
点击【确认】完成计划任务的添加。
在 SQL Server 2005 中配置数据库邮件