正確配置和使用SQL mail

 

使用SQL Mail收發和自動處理郵件中的擴充預存程序簡介

SQL SERVER提供了通過EXCHANGE或OUTLOOK收發郵件的擴充預存程序，下面將這幾個過程簡單的介紹一下。

一、啟動SQL Mail

xp_startmail @user,@password

@user和@password都是可選的

也可開啟Enterprise Manager中的Support Services，在SQL Mail上單擊右鍵開啟右鍵菜單，然後按Start來啟動

二、停止SQL Mail

xp_stopmail

也可用上述方法中的菜單裡的Stop來停止

三、發送郵件

xp_sendmail {[@recipients =] 'recipients [;...n]'}
[,[@message =] 'message>
[,[@query =] 'query>
[,[@attachments =] attachments]
[,[@copy_recipients =] 'copy_recipients [;...n]'
[,[@blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@subject =] 'subject>
[,[@type =] 'type>
[,[@attach_results =] 'attach_value>
[,[@no_output =] 'output_value>
[,[@no_header =] 'header_value>
[,[@width =] width]
[,[@separator =] 'separator>
[,[@echo_error =] 'echo_value>
[,[@set_user =] 'user>
[,[@dbuse =] 'database>

其中@recipients是必需的

參數說明：

參數 說明
@recipients 收件者，中間用逗號分開
@message 要發送的資訊
@query 確定執行並依附郵件的有效查詢，除觸發器中的插入表及刪除表外，此查詢能引用任何對象
@attachments 附件
@copy_recipients 抄送
@blind_copy_recipients 密送
@subject 標題
@attach_results 指定查詢結果做為附件發送
@no_header 不發送查詢結果的列名
@set_user 查詢聯結的使用者名稱，預設為Guset
@dbuse 查詢所用的資料庫，預設為預設資料庫

四、閱讀郵件收件匣中的郵件

xp_readmail [[@msg_id =] 'message_number> [, [@type =] 'type' [OUTPUT]]
[,[@peek =] 'peek>
[,[@suppress_attach =] 'suppress_attach>
[,[@originator =] 'sender' OUTPUT]
[,[@subject =] 'subject' OUTPUT]
[,[@message =] 'message' OUTPUT]
[,[@recipients =] 'recipients [;...n]' OUTPUT]
[,[@cc_list =] 'copy_recipients [;...n]' OUTPUT]
[,[@bcc_list =] 'blind_copy_recipients [;...n]' OUTPUT]
[,[@date_received =] 'date' OUTPUT]
[,[@unread =] 'unread_value' OUTPUT]
[,[@attachments =] 'attachments [;...n]' OUTPUT])
[,[@skip_bytes =] bytes_to_skip OUTPUT]
[,[@msg_length =] length_in_bytes OUTPUT]
[,[@originator_address =] 'sender_address' OUTPUT]]

參數說明：

參數 說明
@originator 寄件者
@subject 主題
@message 資訊
@recipients 收件者
@skip_tytes 讀取郵件資訊時跳過的位元組數，用於順序擷取郵件資訊段。
@msg_length 確定所有資訊的長度，通常與@skip_bytes一起處理長資訊

五、順序處理下一個郵件

xp_findnextmsg [[@msg_id =] 'message_number' [OUTPUT]]
[,[@type =] type]
[,[@unread_only =] 'unread_value> )

六、刪除郵件

xp_deletemail {'message_number'}

如果不指定郵件編號則刪除收件匣中的所有郵件

七、自動處理郵件

sp_processmail [[@subject =] 'subject>
[,[@filetype =] 'filetype>
[,[@separator =] 'separator>
[,[@set_user =] 'user>
[,[@dbuse =] 'dbname>

 

 

>使用者在網上註冊後,系統將隨機產生的密碼發送到使用者登記的Email
>使用者在論壇的文章有回複時將內容發送到使用者的Email
因為上述過程都是在預存程序中完成的，所以避免了前景程式對參數的傳輸處理，也不需要再用第三方的組件完成，感覺比較方便。

1.為了使用SQL mail,首先你的伺服器上得有SMTP服務，我沒有安裝win2000 server內建的SMTP，而是用imail6.04的SMTP,感覺比較穩定，功能也比較強。
2.安裝一個郵件系統,我安裝了outLook 2000，我發現在配置郵件profile時，如果
不安裝outLook而是用別的第三方程式，win2k中文server版在控制台中就找不到“郵件”一項.
3.安裝完outlook後再重新整理控制台,就會找到“郵件”一項，雙擊進行郵件的配置，為設定檔起一個名字(假設為myProfile)，以便以後SQL mail使用，在該設定檔中設定各項屬性。
4.啟動outlook(設定為用myProfile作為預設的設定檔),測試進行收發郵件，確認outlook工作正常。
5.用當前的域帳戶啟動SQL server,在企業管理器的支援服務中，點擊SQL mail的屬性，可以看到在設定檔選擇中，出現了剛才定義的myProfile設定檔(你也可以定義多個profile)，選擇這個設定檔進行測試，SQL將返回成功開始和結束一個MAPI會話的資訊，如果出現錯誤或是沒有找到郵件設定檔，那一定是你啟動SQL server用的帳號有問題
6.現在你就可以在查詢分析器中用XP_sendmail這個擴充預存程序發送SQL mail了，格式如下：
xp_sendmail {[@recipients =] 'recipients [;...n]'}
[,][@message =] 'message>
[,][@query =] 'query>
[,][@attachments =] attachments]
[,][@copy_recipients =] 'copy_recipients [;...n]'
[,][@blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,][@subject =] 'subject>
[,[@type =] 'type>
[,][@attach_results =] 'attach_value>
[,][@no_output =] 'output_value>
[,][@no_header =] 'header_value>
[,][@width =] width]
[,][@separator =] 'separator>
[,][@echo_error =] 'echo_value>
[,][@set_user =] 'user>
[,][@dbuse =] 'database>

其中@recipients是必需的

參數說明：

參數 說明
@recipients 收件者，中間用逗號分開
@message 要發送的資訊
@query 確定執行並依附郵件的有效查詢，除觸發器中的插入表及刪除表外，此查詢能引用任何對象
@attachments 附件
@copy_recipients 抄送
@blind_copy_recipients 密送
@subject 標題
@attach_results 指定查詢結果做為附件發送
@no_header 不發送查詢結果的列名
@set_user 查詢聯結的使用者名稱，預設為Guset
@dbuse 查詢所用的資料庫，預設為預設資料庫

7.不過，如果是在web應用中使用SQL mail,還有一些問題要解決:首先，就是應用程式中串連資料庫的帳號，我在網站程式中的資料庫連接是使用UDL檔案，帳號為DbGuest,這是一個普通帳戶，所以還必須在master庫的擴充預存程序找到XP_sendmail，並在其屬性中增加DbGuest這個使用者，並選擇EXEC許可權。
好了，現在設定完畢，運行網站程式，測試使用者註冊，幾乎沒有什麼延遲，我測試用的郵箱中就收到了這封SQL mail發出的Email:
"謝謝你的註冊,建議你首次登入後修改密碼"

 

 

Sql Mail技術給每一位元據庫開發人員和DBA（資料庫管理員）帶來了極大的方便，利用該技術，Sql Server資料庫代理程式可以在系統出現異常的時候自動發送Email通知管理員，開發人可以利用它讓資料庫自動週期性修改使用者密碼，然後發送Email通知使用者……等等這些應用，都不同程度上把我們從繁雜的工作中解放出來。但是，Sql Mail的配置是比較複雜的，相信90％以上的人在配置Sql Mail的時候都遇到過各種各樣的麻煩，至少有70%的人放棄了Sql Mail而選擇其他方案來解決這個問題。筆者是一名Web開發人員，親身經曆了這一切，並找到了一個更好的替代方法。不敢獨享，寫出來以饗讀者。
Sql Mail配置有幾種方式，按照支援軟體可劃分為基於Exchange、Outlook2000（以上）和第三方軟體的配置方案，三種方式各有利弊，主要表現在以下幾個方面：

    使用Outlook用戶端配合Sql Server實現Sql Mail
    此方案軟體要求較低，只需要在Sql Server所在伺服器上安裝Outlook2000以上版本用戶端即可。它要求在Sql Mail使用期間，OutLook用戶端必須開啟，否則，只能到下次開啟時，郵件才能發送出去。另外，如果伺服器為遠程伺服器，用微軟官方的遠端桌面無法完成配置，可替代的方案是DBA親自去機房直接操作，或者安裝PcAnywhere10替代遠端桌面進行操作。

    使用Exchange要求較高
    Microsoft推薦使用Exchange作為Sql Mail的最佳拍檔，MSDN的資料提出：“由於 POP3/SMTP 協議存在的局限性和登入問題，Microsoft 建議您使用 Exchange Server 來實現可靠性”。但是Exchange並不是專門做這個來使用的，可以說是屈才了，而且Exchange要求伺服器佈建網域管理器，相信這個東東對大多數資料庫伺服器來說用處不大，只不過是浪費資源罷了。如果我們要在多台伺服器上配置Sql Mail那麼就需要在每一台伺服器上佈建網域管理器，或者所有的伺服器都配置到一個域內，但是對於伺服器比較分散的系統來說這樣是不現實的。

    使用第三方系統支援
    微軟MSDN稱：“如果您使用的是第三方郵件伺服器，則必須將郵件伺服器配置為 POP3 伺服器。如果這些郵件伺服器使用的本地郵件服務可能是由第三方郵件用戶端安裝的，Microsoft 將不支援串連到這些伺服器”。這就意味著你還要使用Windows平台的郵件服務，使用ASP編寫網站的朋友一定都知道，Cdonts.dll組件實在是……。
面對這些問題，筆者就變成了我剛才說的那70％了，雖然耙梳無數，讀文字數萬把Sql Mail配置好了，但是我仍然絕對放棄，因為我不想在做第2台、第3台……的時候重蹈覆轍。替代的方案就是Jmail組件＋OLEAutomation 物件，以上的問題迎刃而解。

    預備知識
    1．OLE自動化函數
    OLE自動化使應用程式能夠對另一個應用程式中實現的對象進行操作，或者將對象公開以便可以對其進行操作。自動化用戶端是可對屬於另一個應用程式的公開對象進行操作的應用程式，本文值得是Sql Server。公開對象的應用程式稱為Automation 伺服程式，又成為自動化組件，本文中即Jmail組件咯。用戶端通過訪問應用程式物件的屬性和函數對這些對象進行操作。
    在Sql Server使用Ole組件的途徑是幾個系統擴充預存程序sp_OACreate、sp_OADestroy、sp_OAGetErrorInfo、sp_OAMethod、sp_OASetProperty和sp_OAGetProperty，再次簡單地介紹一下使用方法，詳細資料參考Sql Server聯機叢書。
    OLEAutomation 物件的使用方法：
    （1）調用 sp_OACreate 建立對象。
    格式：sp_OACreate clsid,objecttoken OUTPUT [ , context ]
    參數：clsid——是要建立的 OLE 對象的程式標識符 (ProgID)。此字串描述該 OLE 對象的類，其形式，如 'OLEComponent.Object'，OLEComponent 是 OLE Automation 伺服程式的組件名稱，Object 是 OLE 對象名，本文中使用的“JMail.Message”;
Objecttoken——是返回的對象標誌，並且必須是資料類型為 int 的局部變數。用於標識所建立的 OLE 對象，並將在調用其它 OLE 自動化預存程序時使用。本文中就是通過它來調用JMail.Message組件的屬性和方法的。
    Context——指定新建立的 OLE 對象要在其中啟動並執行執行內容。本文不使用該參數，故不贅述。以下與此一致，所有方法屬性的其他用法請參閱Sql Server聯機文檔。
    （2）使用該對象。
    （a）調用 sp_OAGetProperty 擷取屬性值。
    格式：_OAGetProperty objecttoken,propertyname [, propertyvalue OUTPUT]
    參數：（前面出現過的參數，以下均省略。）
    Propertyname——對象的屬性名稱；
    Propertyvalue——返回的對象的屬性值，該參數帶OUTPUT屬性，執行該操作後，你就可以從propertyvalue中得到屬性的值了。
    （b）調用 sp_OASetProperty 將屬性設為新值。
    格式：sp_OASetProperty objecttoken, propertyname, propertyvalue
    （c）調用 sp_OAMethod 以調用某個方法。
    格式：sp_OAMethod objecttoken, methodname [, returnvalue OUTPUT] [ , [ parametername = ] parametervalue  [...n]]
    參數：Returnvalue——調用方法的傳回值，如果沒有傳回值，此參數設定為NULL；
    Parametername——方法定義中的參數名稱，也就是形參；
    Parametervalue——參數值；
    ……n——表示，可以帶很多參數，個數由方法定義限制；
    （d）調用 sp_OAGetErrorInfo 擷取最新的錯誤資訊。
    格式：sp_OAGetErrorInfo [objecttoken ] [, source OUTPUT] [, description OUTPUT]
    參數：Source——錯誤源；
    Description——錯誤描述；
    （3）調用 sp_OADestroy 釋放對象。
    格式：sp_OADestroy objecttoken

    2．xp_cmdshell擴充預存程序
    該擴充預存程序在master資料庫中，它的全路徑是master..xp_cmdshell（注意，中間是2個點），它的功能是：以作業系統命令列解譯器的方式執行給定的命令字串，並以文本行方式返回任何輸出。
    格式：xp_cmdshell {'command_string'} [, no_output]
    參數：'command_string'——是在作業系統命令列解譯器上執行的命令字串。
    no_output——是選擇性參數，表示執行給定的 command_string，但不向用戶端返回任何輸出。本文應用中不使用該參數。

    操作方法
    （1）軟體準備
    請先到http://www.dimac.net/或者國內提供組件下載的網站下載最新版的JMail組件，如果你得到的是安裝版，執行weJMailx.exe即可，系統的配置安裝程式會自動完成。如果只有一個JMail.dll檔案，請按照下面的步驟安裝：
    （a）建立文字檔，輸入如下命令：
    regsvr32 JMail.dll
    net start w3svc
    另存新檔Install.Bat（注意，千萬不要儲存成Install.Bat.Txt啊）
    （b）此檔案連同Jmail.dll一起拷貝到Sql Server資料庫伺服器的System32目錄下，並執行雙擊Install.Bat即可。
    （2）準備好了嗎？跟我來吧
    （a）運行Sql Server查詢分析器，並以sa身份登入到Sql Server資料庫；
    （b）如果你的預存程序要添加到YourDefaultCatalog資料庫，請在空白Sql視窗輸入如下指令，否則請相應修改資料庫名。
    Use YourDefaultCatalog
    按F5或者運行按鈕運行該指令；
    (c)建立基本發送預存程序
    複製如下代碼到Sql Server命令視窗，並運行。下面的代碼中有相應的注釋，文中不多做解釋，如有疑問請查看前面的“預備知識”或者查詢Sql Server協助檔案，當然也可以和作者聯絡。

Create Procedure dbo.sp_jmail_send
@sender varchar(100),
@sendername varchar(100)='',
@serveraddress varchar(255)='SMTP伺服器位址',
@MailServerUserName varchar(255)=null,
@MailServerPassword varchar(255)=null,
@recipient varchar(255),
@recipientBCC varchar(200)=null,
@recipientBCCName varchar(200)=null,
@recipientCC varchar(200)=null,
@recipientCCName varchar(100)=null,
@attachment varchar(100) =null,
@subject varchar(255),
@mailbody text
As
/*
該預存程序使用辦公自動化指令碼調用Dimac w3 JMail AxtiveX組件來代替Sql Mail發送郵件
該方法支援“伺服器端身分識別驗證”
*/
--聲明w3 JMail使用的常規變數及錯誤資訊變數
Declare @object int,@hr int,@rc int,@output varchar(400),@description varchar (400),@source varchar(400)

--建立JMail.Message對象

Exec @hr = sp_OACreate 'jmail.message', @object OUTPUT

--設定郵件編碼
Exec @hr = sp_OASetProperty @object, 'Charset', 'gb2312'

--身分識別驗證
If Not @MailServerUserName is null
Exec @hr = sp_OASetProperty @object, 'MailServerUserName',@MailServerUserName
If Not @MailServerPassword is null
Exec @hr = sp_OASetProperty @object, 'MailServerPassword',@MailServerPassword

--設定郵件基本參數
Exec @hr = sp_OASetProperty @object, 'From', @sender
Exec @hr = sp_OAMethod @object, 'AddRecipient', NULL , @recipient
Exec @hr = sp_OASetProperty @object, 'Subject', @subject
Exec @hr = sp_OASetProperty @object, 'Body', @mailbody

--設定其它參數
if not @attachment is null
exec @hr = sp_OAMethod @object, 'Addattachment', NULL , @attachment,'false'
print @attachment
If (Not @recipientBCC is null) And (Not @recipientBCCName is null)
Exec @hr = sp_OAMethod @object, 'AddRecipientBCC', NULL , @recipientBCC,@recipientBCCName
Else If Not @recipientBCC is null
Exec @hr = sp_OAMethod @object, 'AddRecipientBCC', NULL , @recipientBCC

If (Not @recipientCC is null) And (Not @recipientCCName is null)
Exec @hr = sp_OAMethod @object, 'AddRecipientCC', NULL , @recipientCC,@recipientCCName
Else If Not @recipientCC is null
Exec @hr = sp_OAMethod @object, 'AddRecipientCC', NULL , @recipientCC

If Not @sendername is null
Exec @hr = sp_OASetProperty @object, 'FromName', @sendername

--調用Send方法發送郵件
Exec @hr = sp_OAMethod @object, 'Send', null,@serveraddress

--捕獲JMail.Message異常
Exec @hr = sp_OAGetErrorInfo @object, @source OUTPUT, @description OUTPUT

if (@hr = 0)
Begin
Set @output='錯誤源: '+@source
Print @output
Select @output = '錯誤描述: ' + @description
Print @output
End
Else
Begin
Print '擷取錯誤資訊失敗！'
Return
End

--釋放JMail.Message對象
Exec @hr = sp_OADestroy @object

    （d）簡化預存程序操作，以適合我們平時的使用習慣
    上面的預存程序基本可以完成郵件發送操作，但是非常冗長，而且不符合我們的習慣，比如它不支援多個發送給接收者、不支援將Sql指令運行結果以附件形式發送（這是Sql Mail的功能，我們也可以做到）等，所以我們要再寫一個預存程序來調用它，以簡化操作，並擴充功能。

Create Procedure SendMail
@Sender varChar(50)=null,
@strRecipients varChar(200),
@strSubject varChar(200),
@strMessage varChar(2000),
@sql varChar(50)=null)
As

Declare @SplitStr varchar(1) --定義郵件地址分割符變數
Declare @strTemp varchar(200) --定義多個收件者字串臨時變數
Declare @email varchar(50) --用分割符分割後的單個收件者字串變數

Declare @SenderAddress varChar(50)
Declare @Attach varChar(200)

Declare @DefaultSender varChar(50)
Declare @MailServer varChar(50)
Declare @User varChar(50)
Declare @Pass varChar(50)
Declare @SenderName varChar(50)
Declare @AttachDir varChar(100)

--初始化預設變數
Set @DefaultSender='預設發送地址'
Set @MailServer='郵件伺服器地址'
Set @User='SMTP伺服器驗證使用者地址'
Set @Pass='SMTP伺服器驗證地址'
Set @SenderName='預設寄件者名稱'
Set @AttachDir='E:\LOG\WebData\Jmail\'+Replace(Replace(Replace(Convert(varChar(19),GetDate(),120),'-',''),' ',''),':','')+'.txt'

--將Email地址分割符統一為分號
set @SplitStr=';'
Set @strTemp=@strRecipients+@SplitStr+'end'
Set @strTemp=Replace(@strTemp,',',';')

--判斷是否有sql語句
If (@Sql is Null) Or (len(@Sql)=0)
Set @AttachDir=Null
Else
Begin
Declare @CmdStr varChar(200)
Set @CmdStr='bcp "'+@Sql+'" queryout '+@AttachDir+' -c'
EXEC master..xp_cmdshell @CmdStr
End

while CharIndex(@SplitStr,@strTemp,1)<>0
Begin
Set @email=left(@strTemp,CharIndex(@SplitStr,@strTemp,1)-1)
Set @strTemp=right(@strTemp,len(@strTemp)-len(@email)-1)
If (@Sender Is Null) Or (Len(@Sender)=0)
Set @SenderAddress=@DefaultSender
Else
Set @SenderAddress=@Sender
Print @email
--調用sp_jmail_send發送郵件
EXEC sp_jmail_send @sender=@SenderAddress,@sendername=@SenderName,
@serveraddress=@MailServer,@MailServerUserName=@User,@MailServerPassword=@Pass,
@recipient=@email,@subject=@strSubject,@mailbody=@strMessage,@attachment=@AttachDir
End
    此預存程序只擴充了Sql查詢結果附件發送，如果你要發送標準附件，請直接使用sp_jmail_send預存程序或者自行擴充功能。