Sql2005数据库邮件功能

Sql2005数据库邮件功能 

在SQL Server 2005 中配置数据库邮件

在公司的OA系统中，报警和提醒功能很弱，为了弥补这一点，考虑利用SQL的数据库邮件功能实现报警，但SQL的数据库邮件配置较复杂，公司采用Exchange作为邮件服务器，域环境也比较复杂，在这样的情况下，尝试了很久都没解决，终于在网上看到下面这篇文章，豁然开朗，所有问题迎刃而解。

这是我在网上找到的最好的一篇关于数据库邮件配置的文章：

SQL Server：在 SQL Server 2005 中配置数据库邮件。

对于真正的 DBA 来说，数据库邮件是必不可少的。 例如，数据库发生了警报（alert）， DBA 希望得到邮件通知，以便即时排除故障。 或者是监控数据库作业（SQL Server Job）的运行状况，当检查到失败的作业时， 就发送数据库邮件报告给 DBA。

在 SQL Server 2000 中 配置 “SQL Mail”，需要安装 Outlook，配置过程比较麻烦。 在 SQL Server 2005 中配置 “Database Mail” 就相对容易多了。 主要是理清思路。

SQL Server 并没有内置邮件服务器（Mail Server）， 它跟我们发送邮件一样，需要用户名和密码通过 SMTP（Simple Message Transfer Protocol） 去连接邮件服务器。我们想让 SQL Server 来发送邮件，首先要告诉它用户名称，密码， 服务器地址，网络传送协议，邮件服务器的端口。。。等信息。这是通过 SQL Server 系统 存储过程 sysmail_add_account_sp 来实现的。

exec sysmail_add_account_sp

这样，在 SQL Server 2005 中就添加了一个发送邮件的帐户。 道理上讲，有了这个邮件帐户，SQL Server 就可以发送邮件了。 如：

sp_send_dbmail @account_name = 'mail_account'

但是，SQL Server 考虑的更周全。试想：如果这个邮件帐户发生故障 （比如：用户密码过期，或者邮件服务器宕机）那岂不是发送不了邮件了？ 为了应对这种情况，SQL Server 2005 引入了 mail profile 这个东东。 一个 profile 中可以包含多个 account （邮件帐户），这样，SQL Server 发邮件的时候会依次尝试 profile 中的多个邮件帐户，如果发送成功，则退出， 否则，利用下一个邮件帐户发送邮件。其中，添加 profile 和 在 account 和 profile 建立映射是通过下面两个系统存储过程实现的：

sysmail_add_profile_spsysmail_add_profileaccount_sp

这时候，SQL Server 发送邮件，就采用下面的方法了：

sp_send_dbmail @profile_name = 'profile_name'

下面是具体的配置邮件步骤

在 sa 系统帐户下运行。

1. 启用 SQL Server 2005 邮件功能。

use mastergoexec sp_configure 'show advanced options',1goreconfiguregoexec sp_configure 'Database mail XPs',1goreconfigurego

2. 在 SQL Server 2005 中添加邮件帐户（account）

exec msdb..sysmail_add_account_sp        @account_name            = 'p.c.w.l'                 -- 邮件帐户名称（SQL Server 使用）       ,@email_address           = '[e-mail]'  -- 发件人邮件地址       ,@display_name            = null                      -- 发件人姓名       ,@replyto_address         = null       ,@description             = null       ,@mailserver_name         = '58.215.64.159'           -- 邮件服务器地址       ,@mailserver_type         = 'SMTP'                    -- 邮件协议（SQL 2005 只支持 SMTP）       ,@port                    = 25                        -- 邮件服务器端口       ,@username                = '[e-mail]'  -- 用户名       ,@password                = 'xxxxxxxxx'               -- 密码       ,@use_default_credentials = 0       ,@enable_ssl              = 0       ,@account_id              = null

3. 在 SQL Server 2005 中添加 profile

exec msdb..sysmail_add_profile_sp @profile_name = 'dba_profile'      -- profile 名称                                  ,@description  = 'dba mail profile' -- profile 描述                                  ,@profile_id   = null

4. 在 SQL Server 2005 中映射 account 和 profile

exec msdb..sysmail_add_profileaccount_sp  @profile_name    = 'dba_profile' -- profile 名称                                          ,@account_name    = 'p.c.w.l'     -- account 名称                                          ,@sequence_number = 1             -- account 在 profile 中顺序

5. 利用 SQL Server 2005 Database Mail 功能发送邮件。

exec msdb..sp_send_dbmail @profile_name =  'dba_profile'               -- profile 名称                          ,@recipients   =  '[e-mail]'          -- 收件人邮箱                          ,@subject      =  'SQL Server 2005 Mail Test' -- 邮件标题                          ,@body         =  'Hello Mail!'               -- 邮件内容                          ,@body_format  =  'TEXT'                      -- 邮件格式

6. 查看邮件发送情况：

use msdbgoselect * from sysmail_allitemsselect * from sysmail_mailitemsselect * from sysmail_event_log

如果不是以 sa 帐户发送邮件，则可能会出现错误：

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

这是因为，当前 SQL Server 登陆帐户（login），在 msdb 数据库中没有发送数据库邮件的权限， 需要加入 msdb 数据库用户，并通过加入 sp_addrolemember 角色赋予权限。假设该SQL Server 登陆帐户 名字为 “dba”

use msdbgocreate user dba for login dbagoexec dbo.sp_addrolemember @rolename   = 'DatabaseMailUserRole',                          @membername = 'dba'go

此时，再次发送数据库邮件，仍可能有错误：

Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 119profile name is not valid

虽然，数据库用户 “dba” 已经在 msdb 中拥有发送邮件的权限了， 但这还不够，他还需要有使用 profile：“dba_profile” 的权限。

use msdbgoexec sysmail_add_principalprofile_sp  @principal_name = 'dba'                                     ,@profile_name   = 'dba_profile'                                     ,@is_default     = 1

从上面的参数 @is_default=1 可以看出，一个数据库用户可以在多个 mail profile 拥有发送权限。

现在，可以利用 SQL Server 2005 发送数据库邮件了吧。

 

 

图文版:

数据库邮件（Database Mail）设计为与 SMTP 服务器配合使用，具有可靠性，灵活性，安全性，兼容性。考虑到Sql Server2005是基于.Net Framework 2.0，而在后者中新增了System.Net.Mail命名空间，相信数据库邮件也是利用了其中的一些功能。接下来就如何启用和配置数据库邮件进行介绍。

    出于安全性的考虑，默认地是不开启数据库功能。启用数据库邮件功能有两种方式：使用SQL Server 外围应用配置器工具或数据库邮件配置向导显式启用数据库邮件。通过外围应用配置器工具启用的方式启动后，选择功能的外围应用配置器，然后启用数据库邮件存储过程。如下图：

    配置数据库邮件是打开Sql Server Management Studio(Sqlwb.exe)，然后在对象资源管理器中的实例下，打开【管理】，然后在【数据库邮件】上右键后点击【配置数据库邮件】，如下图：

    在弹出【欢迎使用数据库邮件配置向导】对话框，在该对话框里单击【下一步】按钮。

    点击【下一步】后出现【新建配置文件】：

   
    然后点击【添加】：

   
   点击【下一步】后管理配置文件安全性，将其设置为默认配置文件：

   
    然后点击【下一步】完成配置：

 

   

    这样就完成了数据库邮件的配置。然后可以在【数据库邮件】上右键后选择【发送测试电子邮件】：

    稍等片刻，如果配置正确，我们就可以收到这个测试邮件了。
   

    我们可以通过在【数据库邮件】上右键选择【查看数据库邮件日志】查看日志：
   

    由于数据库邮件只是通过使用 Microsoft SMTP 服务器进行了测试，从我的测试拉看，好像有的SMTP邮件服务器似乎不能通过，原因不明。由于邮件服务器故障，无法将邮件发送给收件人。 (使用帐户 1 (2007-12-06T11:17:08) 发送邮件。 异常邮件: 无法将邮件发送到邮件服务器。 (不是本地用户；请尝试不同的路径。服务器响应为: auth error. (è??¤óà??꧰ü))。 )

    如果出现：由于邮件服务器故障，无法将邮件发送给收件人。 (使用帐户 1 (2007-12-06T10:08:32) 发送邮件。 异常邮件: 无法将邮件发送到邮件服务器。 (不允许使用邮箱名称。 服务器响应为: You are not authorized to send mail, authentication is required)。 )这样的错误应该是你的SMTP邮件服务器要求认证，而你没有提供正确的帐户密码或者是错误地选择了匿名身份认证（如果SMTP邮件服务器没启用匿名的话）。

    也可以通过：

    SELECT * FROM msdb.dbo.sysmail_allitems

    SELECT * FROM msdb.dbo.sysmail_event_log

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMAIL',
    @recipients = '[e-mail]',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message' ;

    查看邮件发送情况或日志。