SQL Server 2012 RC0 Database Mail簡介
利用 Database Mail 將查詢結果以EMail方式寄出已經不是 SQL Server 的新功能了，不過筆者之前一直沒有把操作步驟記錄下來，這次花點時間做筆記，本文以SQL Server 2012 RC0為例，簡單介紹Database Mail的設定步驟。
開啟 SQL Server Management Studio（以下簡稱SSMS）後首先必須先設定 Database Mail ，由物件總管視窗中【執行個體名稱 > 管理 > Database Mail >設定 Database Mail 】開啟設定視窗。
於歡迎畫面中直接點選【下一步】。
因為是第一次設定筆者使用預設值【執行以下工作以設定 Database Mail 】；若是要修改已經設定好的 Database Mail 內容，可以選擇【管理 Database Mail 帳戶和設定檔】。
於新增設定檔視窗中輸入設定檔名稱，這個名稱將來會在 sp_send_dbmail 中的 @profile_name 屬性中來使用，接著按下加入以設定 SMTP 帳戶資訊。
於新增 Database Mail 帳戶視窗中輸入帳戶名稱、電子郵件地址及伺服器名稱後按確定。
於管理設定檔安全性視窗中可勾選剛剛新增的設定檔名稱，作為預設的設定檔。
可於設定系統參數視窗中您可以各項系統參數設定，筆者為示範方便就依照預設值設定，直接點選下一步。
於完成精靈視窗可以看到Database Mail組態精靈將進行新增帳戶、設定檔、加入帳戶設定檔以及管理設定檔安全性等動作。
若設定作業無誤，將看到下圖的成功訊息。
接著新增查詢輸入下列的T-SQL敘述以開啟進階選項並啟用Database Mail。
1: USE master
2: GO
3:
4: --開啟進階選項
5: EXEC sp_configure 'show advanced option', '1';
6: RECONFIGURE
7: GO
8:
9: --設定啟用Database Mail
10: EXEC sp_configure 'Database Mail XPs','1'
11: RECONFIGURE
12: GO
13:
14: --檢視設定是否成功
15: EXEC sp_configure
16: GO
如此一來就可以利用 sp_send_dbmail 預存程序來寄送 Database Mail，以下節錄自 MSDN 上所示範的三個範例：
- 單純只傳送EMAIL
1: EXEC msdb.dbo.sp_send_dbmail2: @profile_name = 'AdventureWorks2008R2 Administrator',3: @recipients = 'danw@Adventure-Works.com',4: @body = 'The stored procedure finished successfully.',5: @subject = 'Automated Success Message' ;
- 將查詢結果以 EMAIL 附件方式寄出
1: EXEC msdb.dbo.sp_send_dbmail2: @profile_name = 'AdventureWorks2008R2 Administrator',3: @recipients = 'danw@Adventure-Works.com',4: @query = 'SELECT COUNT(*) FROM AdventureWorks2008R2.Production.WorkOrder5: WHERE DueDate > ''2006-04-30''6: AND DATEDIFF(dd, ''2006-04-30'', DueDate) < 2' ,7: @subject = 'Work Order Count',8: @attach_query_result_as_file = 1 ;
- 寄送HTML格式的EMail
1: DECLARE @tableHTML NVARCHAR(MAX) ;2:3: SET @tableHTML =4: N'<H1>Work Order Report</H1>' +5: N'<table border="1">' +6: N'<tr><th>Work Order ID</th><th>Product ID</th>' +7: N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +8: N'<th>Expected Revenue</th></tr>' +9: CAST ( ( SELECT td = wo.WorkOrderID, '',10: td = p.ProductID, '',11: td = p.Name, '',12: td = wo.OrderQty, '',13: td = wo.DueDate, '',14: td = (p.ListPrice - p.StandardCost) * wo.OrderQty15: FROM AdventureWorks2008R2.Production.WorkOrder as wo16: JOIN AdventureWorks2008R2.Production.Product AS p17: ON wo.ProductID = p.ProductID18: WHERE DueDate > '2006-04-30'19: AND DATEDIFF(dd, '2006-04-30', DueDate) < 220: ORDER BY DueDate ASC,21: (p.ListPrice - p.StandardCost) * wo.OrderQty DESC22: FOR XML PATH('tr'), TYPE23: ) AS NVARCHAR(MAX) ) +24: N'</table>' ;25:26: EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',27: @subject = 'Work Order List',28: @body = @tableHTML,29: @body_format = 'HTML' ;
其中以附件方式寄送查詢結果，似乎是把在 SSMS 中以文字顯示結果的內容儲存於附件中寄出。
下列程式碼示範將 Northwind 資料庫中， Region 資料表的查詢結果儲存為 Region.csv 寄給 my@sql.com.tw ，其中查詢結果以csv格式慣用的逗點做分隔。
1: exec sp_send_dbmail @profile_name = 'MyDBMailProfile'
2: ,@recipients = 'my@sql.com.tw'
3: ,@query = 'select * from Northwind.dbo.Region'
4: ,@attach_query_result_as_file = 1
5: ,@query_result_separator = ','
6: ,@query_attachment_filename = 'Region.csv'
收到MAIL的結果如下圖：
跟 SSMS 中以文字顯示結果是不是幾乎完全相同（如下圖）。
節自本文撰寫為止，筆者尚未找到直接以 sp_send_dbmail 將查詢結果以真正的 csv 格式透過 EMail 寄送，不過換個方式先利用 xp_cmdshell 呼叫 bcp 來產生 csv 格式之後，搭配 sp_send_dbmail 的 @file_attachments 參數來夾帶 bcp 匯出的結果，程式碼如下：
1: --啟用xp_cmdshell
2: EXEC sp_configure 'xp_cmdshell','1'
3: RECONFIGURE
4: GO
5:
6: --切換至msdb，因為sp_send_dbmail位於msdb
7: use msdb
8: go
9:
10: declare @output_file varchar(20) = 'd:\Region.csv' --利用bcp匯出的檔案
11: declare @profile varchar(20) = 'MyDBMailProfile' --sp_send_dbmail所使用的Profile
12: declare @to varchar(50) = 'my@sql.com.tw' --收件者
13: declare @bcp varchar(100) = 'bcp Northwind.dbo.Region out ' + @output_file + ' -c -t, -T -S .\denali' --bcp匯出指令
14: exec xp_cmdshell @bcp
15: exec sp_send_dbmail @profile_name = @profile
16: ,@recipients = @to
17: ,@file_attachments = @output_file
18:
19:
20: --為安全起見使用完畢之後關閉xp_cmdshell
21: EXEC sp_configure 'xp_cmdshell','0'
22: RECONFIGURE
23: GO
下圖為收到的查詢結果，這才是真正的 csv 格式。
利用 EXCEL 開啟的結果如下圖：
參考資料：