利用sql server 2005數(shù)據(jù)庫(kù)郵件發(fā)送電子郵件

2024-08-31 00:51:42

字體：大中小

供稿：網(wǎng)友

隨著技術(shù)的不斷進(jìn)步，我們需要知道在你的信息系統(tǒng)發(fā)生了什么，為了滿足這種日益增長(zhǎng)的需求要求有更高級(jí)的方法。

能夠迅速和簡(jiǎn)單地了解形勢(shì)，尤其是了解潛在的問(wèn)題，這對(duì)于我們非常重要。微軟不斷的生產(chǎn)新產(chǎn)品來(lái)傳遞這種能力，在SQL Server 2005中可獲得的一些數(shù)據(jù)庫(kù)特別選項(xiàng)有：查詢通知、通知服務(wù)、服務(wù)代理和數(shù)據(jù)庫(kù)郵件。

數(shù)據(jù)庫(kù)郵件——SQL Server 2005中的一個(gè)新型電子郵件發(fā)送平臺(tái)。在這篇文章的結(jié)尾，為了確保我們的數(shù)據(jù)庫(kù)郵件設(shè)置正常工作，我發(fā)送了一封試驗(yàn)郵件。在本文中，我將基于該郵件來(lái)探討在SQL Server 2005中發(fā)送數(shù)據(jù)庫(kù)郵件的一些高級(jí)功能。

數(shù)據(jù)庫(kù)郵件選項(xiàng)

SQL Server 2005數(shù)據(jù)庫(kù)郵件為發(fā)送電子郵件信息提供了多種選項(xiàng)。這些選項(xiàng)包括：發(fā)送附件，設(shè)置敏感度和重要性，還包括查詢結(jié)果，用HTML格式發(fā)送電子郵件信息。

要在SQL Server 2005中發(fā)送電子郵件，你需要列表A中的腳本來(lái)建立一個(gè)包含一些數(shù)據(jù)的表格，以便稍后能夠使用查詢結(jié)果選項(xiàng)。腳本建立一個(gè)表格并加載一些試驗(yàn)數(shù)據(jù)。

發(fā)送附件

通過(guò)電子郵件發(fā)送文件附件的能力對(duì)工作效率來(lái)說(shuō)是十分重要的。下面的腳本將會(huì)發(fā)送一封電子郵件，其中包含文件名為FileAttachment.txt的附件，給附件存儲(chǔ)于我的C盤驅(qū)動(dòng)器上。

你需要確保文件的存在，這樣發(fā)送過(guò)程就不會(huì)發(fā)生錯(cuò)誤。

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';

敏感度和重要性

通常需要將一封郵件標(biāo)記為敏感或重要，以使得收件人知道這些信息需要小心保護(hù)。下面的腳本將會(huì)發(fā)送一封郵件，敏感度為私人，重要性為高級(jí)。此外，腳本將會(huì)復(fù)制郵件地址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';

查詢結(jié)果

擁有電子郵件發(fā)送能力的數(shù)據(jù)庫(kù)引擎的一個(gè)重要特征是它可以讓你向發(fā)送過(guò)程傳遞一個(gè)查詢。在SQL Mail和Database Mail中都有該特性。Database Mail中的一個(gè)新特性是可以將查詢結(jié)果作為附件，這一點(diǎn)在下面的腳本中展示。如果你不將查詢結(jié)果作為一文件，那么它將被放置在郵件正文中。見(jiàn)列表B。

HTML格式的信件

在數(shù)據(jù)庫(kù)郵件中我最喜愛(ài)的新特征是可以將你的郵件格式化為HTML格式。簡(jiǎn)單看來(lái)，這沒(méi)什么大不了的，但是這確實(shí)帶來(lái)一些很好的可能性。在前面的例子中，你發(fā)送的查詢結(jié)果包含在郵件正文中，因?yàn)樵摾又话涣袛?shù)據(jù)，所以看起來(lái)沒(méi)有什么不方便的。

但是，當(dāng)在查詢結(jié)果集中包含多個(gè)域時(shí)，格式化變得每況愈下。一個(gè)好消息是，可以很簡(jiǎn)單的使用SQL Server 2005中的一些xml和HTML新特征來(lái)格式化你的查詢結(jié)果集，因此，可以很容易的在信件中查看查詢結(jié)果。列表C中的腳本演示了如何利用查詢結(jié)果發(fā)送一封XML/HTML格式的電子郵件。

當(dāng)你發(fā)送HTML格式的郵件時(shí)，允許你將查詢結(jié)果嵌入到郵件的查詢部分，從而可以將查詢結(jié)果格式化成為更容易看的形式。（雖然，你可能會(huì)爭(zhēng)論說(shuō)我在信件中使用的黃色背景，看起來(lái)并不是很舒服。）

可能性

當(dāng)你能夠從數(shù)據(jù)庫(kù)引擎發(fā)送電子郵件時(shí)，這就引起了很多可能性。加上一些額外的邏輯和構(gòu)建，你就可以編寫一個(gè)國(guó)產(chǎn)郵件系統(tǒng)，這樣就可以確保你總能找到想要找的人。在我的關(guān)于數(shù)據(jù)庫(kù)郵件系列三中，我將介紹如何書寫這樣一個(gè)應(yīng)用。

Tim chapman是在位于路易斯維爾一家銀行工作的SQL Server數(shù)據(jù)庫(kù)管理員，有7年多的IT經(jīng)驗(yàn)，同時(shí)，他還獲得了微軟SQL Server 2000 和SQL Server 2005認(rèn)證。

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'
 
 
下面是另一網(wǎng)站內(nèi)容
 
數(shù)據(jù)庫(kù)郵件（Database Mail）是SQL Server 2005數(shù)據(jù)庫(kù)引擎中新增的一項(xiàng)簡(jiǎn)單實(shí)用的功能。Database Mail代替了SQL Mail，它使用一個(gè)簡(jiǎn)單郵件傳輸協(xié)議（SMTP）服務(wù)器，而不是SQL Mail所要求的MAPI賬號(hào)來(lái)發(fā)送電子郵件。 
 
這允許你的組織發(fā)送帶附件和查詢結(jié)果的電子郵件，附加查詢結(jié)果，以及格式化HTML電子郵件。你還可以用它設(shè)定許多其它配置，而不需要你擁有一臺(tái)Exchange服務(wù)器或配置任何類型的MAPI工作區(qū)。 
 
使用Database Mail的好處 
 
除完全以SMTP為基礎(chǔ)外，Database Mail還具有許多其它優(yōu)點(diǎn)： 
 
它在數(shù)據(jù)庫(kù)引擎以外運(yùn)行，因此對(duì)數(shù)據(jù)庫(kù)引擎的壓力最小。 
它支持群集，完全支持群集環(huán)境。 
它的用戶資料（Profile）允許使用冗余SMTP服務(wù)器。（我將在本文后部分詳細(xì)討論這一點(diǎn)。） 
它允許你以參數(shù)的形式向存儲(chǔ)過(guò)程發(fā)送查詢文本，存儲(chǔ)過(guò)程將執(zhí)行查詢并在電子郵件中發(fā)送結(jié)果。 
消息通過(guò)一個(gè)Service Broker隊(duì)列異步傳送，因此你在發(fā)送電子郵件時(shí)不必等待回應(yīng)。 
它為電子郵件發(fā)送提供多重安全保護(hù)，如一個(gè)控制附件擴(kuò)展名的過(guò)濾器和一個(gè)附件大小管理器。 
建立和使用Database Mail 
 
在建立一個(gè)Database Mail解決方案前，你需要進(jìn)行一些規(guī)劃工作。首先，你必須具有一臺(tái)有效的SMTP服務(wù)器來(lái)傳送電子郵件。如果你沒(méi)有SMTP服務(wù)器，請(qǐng)參閱微軟知識(shí)庫(kù)文章308161了解建立SMTP服務(wù)器的相關(guān)信息。如果你無(wú)法確定組織是否擁有SMTP服務(wù)器，詢問(wèn)你的網(wǎng)絡(luò)管理員獲得機(jī)器名稱或服務(wù)器的IP地址。你的網(wǎng)絡(luò)管理員可能需要對(duì)服務(wù)器進(jìn)行配置，以便SQL Server能夠發(fā)送電子郵件。 
 
在Database Mail中，賬戶（Account）保存數(shù)據(jù)庫(kù)引擎用來(lái)發(fā)送電子郵件消息的信息。一個(gè)賬戶只為一臺(tái)電子郵件服務(wù)器保存信息，如賬戶名、電子郵件地址、回復(fù)電子郵件地址、服務(wù)器名稱或IP地址，以及一些可選的安全設(shè)置。 
 
要發(fā)送一封Database Mail電子郵件，必須使用一個(gè)用戶資料（Profile）。用戶資料為一個(gè)或幾個(gè)賬戶設(shè)立。這種用戶資料-賬戶設(shè)置非常有用。它允許你將幾個(gè)賬戶和一個(gè)用戶資料聯(lián)系起來(lái)，這意味著你可以將幾臺(tái)電子郵件服務(wù)器和一個(gè)用戶資料聯(lián)系起來(lái)。 
 
因此，當(dāng)你試圖發(fā)送一封電子郵件時(shí)，系統(tǒng)會(huì)嘗試用戶資料中的每個(gè)賬戶，直到消息被成功發(fā)送出去。如果一臺(tái)或幾臺(tái)SMTP服務(wù)器出現(xiàn)故障，這種設(shè)置就十分有用。它還允許你開發(fā)發(fā)送電子郵件的應(yīng)用程序代碼，而不必?fù)?dān)心針對(duì)不同的環(huán)境修改Profile名稱。你可以在開發(fā)和生產(chǎn)環(huán)境中使用相同的Profile名稱，唯一的差別在于用戶資料中包含的賬戶有所不同。 
 
該是時(shí)候了解如何建立一個(gè)Database Mail賬戶了。在我們的例子中，我假設(shè)你正坐在一臺(tái)你具有系統(tǒng)管理員訪問(wèn)權(quán)限的開發(fā)機(jī)器前。如果你沒(méi)有系統(tǒng)管理員權(quán)限，你需要成為msdb數(shù)據(jù)庫(kù)DatabaseMailUserRole的一員。 
 
下面的腳本建立一些我在整個(gè)實(shí)例中都要用到的變量。注意：整個(gè)腳本將在msdb數(shù)據(jù)庫(kù)中運(yùn)行，Database Mail對(duì)象就保存在其中。 
 
USE msdbGODECLARE @ProfileName VARCHAR(255)DECLARE @AccountName VARCHAR(255)DECLARE @SMTPAddress VARCHAR(255)DECLARE @EmailAddressVARCHAR(128)DECLARE @DisplayUser VARCHAR(128)這里我建立了ProfileName、AccountName、STMP服務(wù)器名稱以及顯示在電子郵件From（郵件來(lái)自）框中的地址。 
 
SET @ProfileName = 'DBMailProfile';SET @AccountName = 'DBMailAccount';SET @SMTPAddress = 'mail.yoursmtpserver.com';SET @EmailAddress = 'DBMail@yoursmtpserver.com';SET @DisplayUser = 'The Mail Man';下面的的腳本完成一些清理工作，以便如果我再次運(yùn)行腳本，就不必?fù)?dān)心出現(xiàn)錯(cuò)誤。 
 
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下面的腳本給系統(tǒng)增加賬戶（Account）、用戶資料（Profile）和賬戶-用戶資料（Account-Profile）關(guān)系。 
 
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 ;現(xiàn)在一切都已經(jīng)準(zhǔn)備妥當(dāng)，我將發(fā)送一封測(cè)試電子郵件。 
 
EXEC msdb.dbo.sp_send_dbmail@recipients=N'chapman.tim@gmail.com',@body= 'Test Email Body', @subject = 'Test Email Subject',@profile_name = @ProfileName要檢查消息是否發(fā)送成功，我可以對(duì)sysmail_allitems系統(tǒng)視圖執(zhí)行一次查詢。 
 
SELECT * FROM sysmail_allitems（T004）