方便好用的Database Mail – SQL2005

有在用 SQL 2000的使用者還記得 SQL Mail吧. SQL Mail是利用MAPI的方式進行mail的傳遞, 在實務上往往會使用outlook配合exchange server來進行環境的建立, 也造成在使用SQL Mail前, 要準備好這些環境. 但在一般的應用上, 要使用SQL Mail, 又要有這些環境, 不見得很容易實現. 另外再加上SQL Mail在cluster環境時, 也要注意兩台的MAPI profile的設定, 才不會發生發不出mail的問題.

是的, 你的不方便, MS都知道, 到 SQL 2005後, 你會發現多了一個 Database Mail 的功能, 本篇文章就是來介紹這個方便又好用的 SQL Server發信方式. Database Mail 是基於 SMTP的方式來寄送信件, 所以很容易設定, 不太需要像 MAPI 需要 outlook 介面及 exchange server 配合寄送, 當然還是要建立 profile, 好讓 SQL Server 明白你要發信的帳號. 不過因為是走 SMTP, 所以設定也就容易多了. 而且 Database Mail 是能完全支援 cluster 的(因為是用 SQL Server 的機制, 而非外部的資源).

我們在 SQL Server Management Studio 上展開你要設定的 SQL Server, 在 Management 下有個 Database Mail, 接下來就按右鍵進行設置 profile 及發送測試信件囉, 步驟可參考 MS 說明:

http://msdn2.microsoft.com/en-us/library/ms175951.aspx
中文: http://msdn2.microsoft.com/zh-tw/library/ms175951.aspx

設定完成後, 我們來看看發個 mail 試試囉, 利用 sp_send_mail 來進行發送 mail 囉. 為什麼會要用 SQL Mail 或 Database Mail, 其中除了整合在 SQL Server 外, 很重要的一點是將 query 的結果放入 mail 裡, 方便通知或警示, 這是十分重要的功能, 因為在管理資料庫時, 有時需要發生問題時, 當時的環境及資料的狀況, 利用 query 來調出資料是十分重要方便的. 所以要學會使用 SQL Mail 或 Database Mail 相信大家也能體會這個很重要吧!

sp_send_mail 範例如下:

msdb..sp_send_dbmail
   @profile_name = 'myprofile',
   @recipients = 'op1@diary.tw',
   @query = 'select top 10 * from test..member where email is null' ,
   @subject = 'member data email null',
   @body = 'member data email null notification',
   @attach_query_result_as_file = 1,
   @body_format = 'HTML' ;

上面是一個比較常用, 帶著 query 的結果為附件的用法, 實際測試時發現, 要將查詢結果變為附件時, body_format 要為 HTML 才行, 否則似乎是會發(從發送記錄上可以查得到), 但收不到, 所以在做 sp_send_dbmail 時, 要注意一下. 純文字的話, 就不要用查詢附件夾檔囉, 如下:

msdb..sp_send_dbmail
   @profile_name = 'myprofile',
   @recipients = 'op1@diary.tw',
   @query = 'select top 10 * from test..member where email is null' ,
   @subject = 'member data email null',
   @body = 'member data email null notification'  ;

利用這種方式, 查詢的內容將會放在 mail 的 body 裡面, 而不用夾檔的方式.
接下來說明相關的 table, 使用 Database Mail 後, log 會放在 msdb 這個系統資料庫內, 相關的 table 比較會用到的有以下三個:

1) msdb.dbo.sysmail_allitems
2) msdb.dbo.sysmail_attachments
3) msdb.dbo.sysmail_event_log

其中 1) 是用來存放所有寄送的 mail, 2) 是存放有附件的 mail 附件, 3) 則是系統的記錄. 欄位功能看名稱即可得知. 要特別注意的是存放在 msdb 的相關資料表會愈來愈大, 這個可藉由後面的[如何建立 job 封存 Database Mail 的記錄]這篇文章獲得解決, 當然在設定 Database Mail profile時也可以在 Database Mail 組態精靈中[檢視或變更系統參數]功能來修改[記錄層次]來減少記錄的資料, 但 mail 本身的記錄應該都還是會存在的, 這是在使用 Database Mail 所衍生的空間使用問題, 得特別注意的地方.

繼續閱讀:
Database Mail 特性:
http://msdn2.microsoft.com/en-us/library/ms175887.aspx
中文: http://msdn2.microsoft.com/zh-tw/library/ms175887.aspx

如何將 SQL Mail 的 stored procedure 轉換為 Database Mail:
http://msdn2.microsoft.com/en-us/library/ms187891.aspx
中文: http://msdn2.microsoft.com/zh-tw/library/ms187891.aspx

如何建立 job 封存 Database Mail 的記錄:
http://msdn2.microsoft.com/en-us/library/ms189258.aspx
中文: http://msdn2.microsoft.com/zh-tw/library/ms189258.aspx

參考範例:
http://www.databasejournal.com/features/mssql/article.php/3626056