利用SQL Server 2005数据库邮件发送电子邮件-控件新闻-慧都网

利用SQL Server 2005数据库邮件发送电子邮件

翻译|其它|编辑：郝浩|2008-01-24 10:21:58.000|阅读 1174 次

概述：

随着技术的不断进步，我们需要知道在你的信息系统发生了什么，为了满足这种日益增长的需求要求有更高级的方法。

能够迅速和简单地了解形势，尤其是了解潜在的问题，这对于我们非常重要。微软不断的生产新产品来传递这种能力，在SQL Server 2005中可获得的一些数据库特别选项有：查询通知、通知服务、服务代理和数据库邮件。

数据库邮件——SQL Server 2005中的一个新型电子邮件发送平台。在这篇文章的结尾，为了确保我们的数据库邮件设置正常工作，我发送了一封试验邮件。在本文中，我将基于该邮件来探讨在SQL Server 2005中发送数据库邮件的一些高级功能。

数据库邮件选项

SQL Server 2005数据库邮件为发送电子邮件信息提供了多种选项。这些选项包括：发送附件，设置敏感度和重要性，还包括查询结果，用HTML格式发送电子邮件信息。

要在SQL Server 2005中发送电子邮件，你需要列表A中的脚本来建立一个包含一些数据的表格，以便稍后能够使用查询结果选项。脚本建立一个表格并加载一些试验数据。

发送附件

通过电子邮件发送文件附件的能力对工作效率来说是十分重要的。下面的脚本将会发送一封电子邮件，其中包含文件名为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。

HTML格式的信件

在数据库邮件中我最喜爱的新特征是可以将你的邮件格式化为HTML格式。简单看来，这没什么大不了的，但是这确实带来一些很好的可能性。在前面的例子中，你发送的查询结果包含在邮件正文中，因为该例子只包含一列数据，所以看起来没有什么不方便的。

但是，当在查询结果集中包含多个域时，格式化变得每况愈下。一个好消息是，可以很简单的使用SQL Server 2005中的一些XML和HTML新特征来格式化你的查询结果集，因此，可以很容易的在信件中查看查询结果。列表C中的脚本演示了如何利用查询结果发送一封XML/HTML格式的电子邮件。

当你发送HTML格式的邮件时，允许你将查询结果嵌入到邮件的查询部分，从而可以将查询结果格式化成为更容易看的形式。（虽然，你可能会争论说我在信件中使用的黄色背景，看起来并不是很舒服。）

可能性

当你能够从数据库引擎发送电子邮件时，这就引起了很多可能性。加上一些额外的逻辑和构建，你就可以编写一个国产邮件系统，这样就可以确保你总能找到想要找的人。在我的关于数据库邮件系列三中，我将介绍如何书写这样一个应用。

Tim chapman是在位于路易斯维尔一家银行工作的SQL Server数据库管理员，有7年多的IT经验，同时，他还获得了微软SQL Server 2000 和SQL Server 2005认证。

IF EXISTS(SELECT name from master..sysdatabases where name = 'SalesDB')
      DROP DATABASE SalesDB

CREATE DATABASE SalesDB;

USE SALESDB;
CREATE TABLE SalesHistory
(
      SaleID INT IDENTITY(1,1),
      Product VARCHAR(30), 
      SaleDate SMALLDATETIME, 
      SalePrice MONEY
)

DECLARE @i SMALLINT

SET @i = 1

WHILE (@i <=100)

BEGIN
      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)
      VALUES
      ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57) )


      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)
      VALUES
      ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13) )


      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)
      VALUES
      ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29) )


      SET @i = @i + 1

END

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'
 
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'
 
 
下面是另一网站内容
 
数据库邮件（Database Mail）是SQL Server 2005数据库引擎中新增的一项简单实用的功能。Database Mail代替了SQL Mail，它使用一个简单邮件传输协议（SMTP）服务器，而不是SQL Mail所要求的MAPI账号来发送电子邮件。
这允许你的组织发送带附件和查询结果的电子邮件，附加查询结果，以及格式化HTML电子邮件。你还可以用它设定许多其它配置，而不需要你拥有一台Exchange服务器或配置任何类型的MAPI工作区。
使用Database Mail的好处
除完全以SMTP为基础外，Database Mail还具有许多其它优点：
它在数据库引擎以外运行，因此对数据库引擎的压力最小。
它支持群集，完全支持群集环境。
它的用户资料（Profile）允许使用冗余SMTP服务器。（我将在本文后部分详细讨论这一点。）
它允许你以参数的形式向存储过程发送查询文本，存储过程将执行查询并在电子邮件中发送结果。
消息通过一个Service Broker队列异步传送，因此你在发送电子邮件时不必等待回应。
它为电子邮件发送提供多重安全保护，如一个控制附件扩展名的过滤器和一个附件大小管理器。
建立和使用Database Mail
在建立一个Database Mail解决方案前，你需要进行一些规划工作。首先，你必须具有一台有效的SMTP服务器来传送电子邮件。如果你没有SMTP服务器，请参阅微软知识库文章308161了解建立SMTP服务器的相关信息。如果你无法确定组织是否拥有SMTP服务器，询问你的网络管理员获得机器名称或服务器的IP地址。你的网络管理员可能需要对服务器进行配置，以便SQL Server能够发送电子邮件。
在Database Mail中，账户（Account）保存数据库引擎用来发送电子邮件消息的信息。一个账户只为一台电子邮件服务器保存信息，如账户名、电子邮件地址、回复电子邮件地址、服务器名称或IP地址，以及一些可选的安全设置。
要发送一封Database Mail电子邮件，必须使用一个用户资料（Profile）。用户资料为一个或几个账户设立。这种用户资料-账户设置非常有用。它允许你将几个账户和一个用户资料联系起来，这意味着你可以将几台电子邮件服务器和一个用户资料联系起来。
因此，当你试图发送一封电子邮件时，系统会尝试用户资料中的每个账户，直到消息被成功发送出去。如果一台或几台SMTP服务器出现故障，这种设置就十分有用。它还允许你开发发送电子邮件的应用程序代码，而不必担心针对不同的环境修改Profile名称。你可以在开发和生产环境中使用相同的Profile名称，唯一的差别在于用户资料中包含的账户有所不同。
该是时候了解如何建立一个Database Mail账户了。在我们的例子中，我假设你正坐在一台你具有系统管理员访问权限的开发机器前。如果你没有系统管理员权限，你需要成为msdb数据库DatabaseMailUserRole的一员。
下面的脚本建立一些我在整个实例中都要用到的变量。注意：整个脚本将在msdb数据库中运行，Database Mail对象就保存在其中。
USE msdbGODECLARE @ProfileName VARCHAR(255)DECLARE @AccountName VARCHAR(255)DECLARE @SMTPAddress VARCHAR(255)DECLARE @EmailAddressVARCHAR(128)DECLARE @DisplayUser VARCHAR(128)




这里我建立了ProfileName、AccountName、STMP服务器名称以及显示在电子邮件From（邮件来自）框中的地址。

    
        
            
            SET @ProfileName = 'DBMailProfile';SET @AccountName = 'DBMailAccount';SET @SMTPAddress = 'mail.yoursmtpserver.com';SET @EmailAddress = 'DBMail@yoursmtpserver.com';SET @DisplayUser = 'The Mail Man';
            
        
    

下面的的脚本完成一些清理工作，以便如果我再次运行脚本，就不必担心出现错误。

    
        
            
            IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profileaccount pa      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_idWHERE      p.name = @ProfileName AND      a.name = @AccountName)BEGIN      PRINT 'Deleting Profile Account'      EXECUTE sysmail_delete_profileaccount_sp      @profile_name = @ProfileName,      @account_name = @AccountNameENDIF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)BEGIN      PRINT 'Deleting Profile.'      EXECUTE sysmail_delete_profile_sp      @profile_name = @ProfileNameENDIF EXISTS(SELECT * FROM msdb.dbo.sysmail_account aWHERE a.name = @AccountName)BEGIN      PRINT 'Deleting Account.'     EXECUTE sysmail_delete_account_sp      @account_name = @AccountNameEND
            
        
    

下面的脚本给系统增加账户（Account）、用户资料（Profile）和账户-用户资料（Account-Profile）关系。

    
        
            
            EXECUTE msdb.dbo.sysmail_add_account_sp@account_name = @AccountName,@email_address = @EmailAddress,@display_name = @DisplayUser,@mailserver_name = @SMTPAddressEXECUTE msdb.dbo.sysmail_add_profile_sp@profile_name = @ProfileName EXECUTE msdb.dbo.sysmail_add_profileaccount_sp@profile_name = @ProfileName,@account_name = @AccountName,@sequence_number = 1 ;
            
        
    

现在一切都已经准备妥当，我将发送一封测试电子邮件。

    
        
            
            EXEC msdb.dbo.sp_send_dbmail@recipients=N'chapman.tim@gmail.com',@body= 'Test Email Body', @subject = 'Test Email Subject',@profile_name = @ProfileName
            
        
    

要检查消息是否发送成功，我可以对sysmail_allitems系统视图执行一次查询。

    
        
            
            SELECT * FROM sysmail_allitems