Transact-SQL Database Mail 實戰篇

一般我們發送 Email 都是藉由 Mail Server 發送；但是，若資料庫在運行過程中發生了狀況，而又需要管理人員處理時，Database Mail 就是最佳的選擇了。也許有人納悶，不是由 SQL Server Management Studio 設定就可以了? 當然，如果您是資料庫管理人員，而又使用付費版的 SQL Server 時，便可以用這種介面進行設定。但若您是免費版的 SQL Server Express 使用者，或是希望在程式中加入這種功能，Management Studio 就無能為力了。因此本文將針對 SQL Server 發送 Email 的相關指令進行介紹，並附上 "SQL Server 2008 呼叫 Gmail 發送 Email" 實作範例供大家參考。

1) 前置作業：在開始介紹前，請先確認 DatabaseMail.exe, atabaseMailengine.dll, DatabaseMailprotocols.dll 3 個檔案已存在於 SQL Server 的 Binn 目錄中。若您的 SQL Server 2008 R2 為已付費版，請至 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn；使用 Express 版本者，請至 C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn 檢查這 3 個檔案是否存在。SQL Server 就是利用它們來進行 Email 的發送作業。


2) 啟動/停止 Database Mail

-- 啟動
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'Database Mail XPs', 1
reconfigure
exec msdb.dbo.sysmail_start_sp;

-- 停止
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'Database Mail XPs', 1
reconfigure
exec msdb.dbo.sysmail_stop_sp;

3) 郵件帳號：設定欲發送訊息的帳號資訊，包括 "發送者的 Email (帳號 & 密碼)"、"欲透過哪個 Mail Server 發送"、"欲發送至哪個 Email"、"收件者回覆到哪個 Email" 等。

-- 建立郵件帳號
exec msdb.dbo.sysmail_add_account_sp
     @account_name      = 'Your DBA Account',
     @description       = 'Database Administrator Account',
     @email_address     = 'xxx@gmail.com',
     @replyto_address   = 'xxx@gmail.com',
     @display_name      = 'TEST',
     @mailserver_name   = 'smtp.gmail.com',
     @port              = 587,
     @username          = 'xxx@gmail.com',
     @password          = 'Your Gmail Password',
     @enable_ssl        = 1

-- 刪除郵件帳號
exec msdb.dbo.sysmail_delete_account_sp @account_name = 'Your DBA Account'

4) 郵件設定檔：Database Mail 將依照指定的設定檔來發送給一個或多個 Email Address。

-- 建立設定檔
exec msdb.dbo.sysmail_add_profile_sp
     @profile_name = 'Your DBA Profile',
     @description  = 'Database Administrator'

-- 刪除設定檔
exec msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Your DBA Profile'

-- 建立 Profile 與 Account 的連接
exec msdb.dbo.sysmail_add_profileaccount_sp
     @profile_name     = 'Your DBA Profile',
     @account_name     = 'Your DBA Account',
     @sequence_number  = 1

-- 授予 DBMailUsers 存取設定檔的權限
exec msdb.dbo.sysmail_add_principalprofile_sp
     @profile_name     = 'Your DBA Profile',
     @principal_name   = 'public',
     @is_default = 1

-- 刪除 Profile 與 Account 的連接
exec msdb.dbo.sysmail_delete_profileaccount_sp 
     @profile_name = 'Your DBA Profile', 
     @account_name = 'Your DBA Account'

5) 發送 Email：依郵件設定檔發送 Email

exec msdb.dbo.sp_send_dbmail
     @recipients   =  N'yyy@hotmail.com',
     @body         = 'Database Email Testing',
     @subject      = 'Alert',
     @profile_name = 'Your DBA Profile'  

6) 狀態檢查

-- Profiles 狀態 
select * from msdb.dbo.sysmail_profile

-- Accounts 狀態 
select * from msdb.dbo.sysmail_account

-- Profile & Accounts 連接狀態 
select * from msdb.dbo.sysmail_profileaccount

-- Principal Profile 狀態 
select * from msdb.dbo.sysmail_principalprofile

-- Mail Server 狀態 
select * from msdb.dbo.sysmail_server
select * from msdb.dbo.sysmail_servertype
select * from msdb.dbo.sysmail_configuration

-- 檢查 Email 發送狀態 ----------------------------------------------
-- 顯示全部
select * from msdb.dbo.sysmail_allitems
-- 已送出
select * from msdb.dbo.sysmail_sentitems
-- 尚未送出
select * from msdb.dbo.sysmail_unsentitems
-- 發送失敗
select * from msdb.dbo.sysmail_faileditems  

7) 刪除 LOG 紀錄：呼叫 sysmail_delete_log_sp，藉由傳入不同的 @event_type 參數值來刪除不同種類的紀錄。呼叫方式為 exec msdb.dbo.sysmail_delete_log_sp @event_type = 'zzz'
當 zzz = success 時，刪除成功紀錄。(例如: exec msdb.dbo.sysmail_delete_log_sp @event_type = 'success')
當 zzz = warning 時，刪除警告紀錄。
當 zzz = error 時，刪除錯誤成功紀錄。
當 zzz = information 時，刪除資訊紀錄。


8) 刪除郵件：呼叫 sysmail_delete_mailitems_sp，藉由傳入不同的 @sent_status 參數值來刪除不同種類的發送紀錄。呼叫方式為 exec msdb.dbo.sysmail_delete_mailitems_sp @sent_status = 'zzz'
當 zzz = sent 時，刪除已成功發送的郵件。(例如: exec msdb.dbo.sysmail_delete_mailitems_sp @sent_status = 'sent')
當 zzz = retrying 時，刪除正在重試的郵件。
當 zzz = unsent 時，刪除尚未發送的郵件。
當 zzz = failed 時，刪除發送失敗的郵件。

實作範例

藉由 Gmail 帳號發送 Database Mail 給 Hotmail 帳號。請先進入該機 DOS Command 模式中，輸入 ping smtp.gmail.com 檢查您的 SQL Server 是否可連至 Gmail 。 (若顯示 "回覆自..." 等字樣，則表示可連上。)

-- 啟動 -------------------------------------------------------------
exec sp_configure 'show advanced options', 1
reconfigure

exec sp_configure 'Database Mail XPs', 1
reconfigure

exec msdb.dbo.sysmail_start_sp; 

-- 建立郵件帳號 -----------------------------------------------------
exec msdb.dbo.sysmail_add_account_sp
     @account_name      = 'Your DBA Account',
     @description       = 'Database Administrator Account',
     @email_address     = 'xxx@gmail.com',
     @replyto_address   = 'xxx@gmail.com',
     @display_name      = 'TEST',
     @mailserver_name   = 'smtp.gmail.com',
     @port              = 587,
     @username          = 'xxx@gmail.com',
     @password          = 'Your Gmail Password',
     @enable_ssl        = 1
     
-- 建立設定檔 -------------------------------------------------------
exec msdb.dbo.sysmail_add_profile_sp
     @profile_name      = 'Your DBA Profile',
     @description       = 'Database Administrator'
     
-- 建立 Profile 與 Account 的連接 -----------------------------------
exec msdb.dbo.sysmail_add_profileaccount_sp
     @profile_name      = 'Your DBA Profile',
     @account_name      = 'Your DBA Account',
     @sequence_number   = 1

-- 授予 DBMailUsers 存取設定檔的權限 --------------------------------
exec msdb.dbo.sysmail_add_principalprofile_sp
     @profile_name      = 'Your DBA Profile',
     @principal_name    = 'public',
     @is_default = 1     
     
-- 發送 Email：依郵件設定檔發送 Email -------------------------------
exec msdb.dbo.sp_send_dbmail
     @recipients   =  N'xxx@hotmail.com',
     @body         = 'Database Email Testing',
     @subject      = 'Alert',
     @profile_name = 'Your DBA Profile' 
     
-- 檢查 Email 發送狀態 ----------------------------------------------
select * from msdb.dbo.sysmail_allitems      -- 顯示全部
select * from msdb.dbo.sysmail_sentitems     -- 已送出
select * from msdb.dbo.sysmail_unsentitems   -- 尚未送出
select * from msdb.dbo.sysmail_faileditems   -- 發送失敗

如欲參考其他應用範例，請見另一篇文章：SQL Server 2008 Express 自動化管理 (三) 異常警示