信IT德永生

在以往發送mail的事，我是想說在程式中，或ssis中做

拜讀了楊志強老師的書後，發現原來在sql server中也可以啟用db mail的功能來由sql server直接撈資料後發mail，且還有支援html的格式，比起ssis的陽春訊息還要好呢！

以下就來個範例介紹吧

首先如果你是使用sql 2005的話要先啟用這項功能

如果是sql sever2008的話，如下圖點了設定database mail之後

以下的範例皆是以sql server 2008當作範例說明

如果是第一次，則會提示你是否要啟用該功能，按是即可

接下來會出現精靈

如果是第一次設定，那就選第一項吧

先輸入你想要的設定檔名稱

之後按加入，設定方式如下，注意，基本驗証的地方，請輸入可以驗証的smtp帳密，若是domain帳號，請記得使用者名稱上要加上domain名稱ex: ms\jack

設定完後接下來我們選擇測試一下mail是否正常可以送出

輸入收件者的mail帳號

如果上述的測試是成功的，那就可以開始撰寫sql code了

我們新增一個查詢頁面將以下的sql語法碼貼上

exec msdb.dbo.sp_send_dbmail
@profile_name='test',   --設定檔
@recipients='rick@ms.com', --收件者
@subject='mail測試',  --主旨
@body='測試', --內文
@query='select getdate()',  --還可以下查詢式哦
@file_attachments='C:\test.txt',  --夾檔
@attach_query_result_as_file=1,  --把查詢的結果設為附件夾檔，不設的話就是在mail內容中看到囉
@body_format=TEXT    --使用text格式
--@body_format=HTML'  --也可以使用HTML格式
go

注意如果你的body_format是使用 html，且是使用outlook express收信的話，記得要將”檢視”->”HTML格式的郵件”選項打開，不然看到到亂碼，若是使用outlook則不會。

利用以上的方式，我們可以將這段寫成一個storedprocedre的方式，在裡面執行撈資料後以跑迴圈的方式發mail給客戶囉

以下為我自己寫的一段storedproced程式碼，將這段程式碼稍微修改一下後，使用sql server的agent來指定這樣，就可以自動發送mail囉！不一定要靠ssis或程式也可以發mail，是不是很方便呢？sql server真是好物..

		   1: SET ANSI_NULLS ON

		   2: GO

		   3: SET QUOTED_IDENTIFIER ON

		   4: GO

		   5: --

		   6: --exec SendMail

		   7: --

		   8: Create PROCEDURE [dbo].[SendMail] as 

		   9: DECLARE @mail varchar(50)

		  10: DECLARE @msg varchar(255)

		  11: DECLARE @titile varchar(255)

		  12: DECLARE @MyCursor CURSOR 

		  13: SET @MyCursor = CURSOR FAST_FORWARD

		  14:  

		  15: FOR 

		  16: select name,mail,bodymsg from mail

		  17: OPEN @MyCursor

		  18:  

		  19: --取得第一筆的值放到變數

		  20: FETCH NEXT FROM @MyCursor

		  21: INTO @titile,@mail,@msg

		  22: WHILE @@FETCH_STATUS = 0

		  23:  

		  24: BEGIN 

		  25: set @msg='<B><font size=7>'+@msg+'</B></font>'

		  26: exec msdb.dbo.sp_send_dbmail

		  27: @profile_name='test',   --設定檔

		  28: @recipients=@mail, --收件者

		  29: @subject=@titile,  --主旨

		  30:  

		  31: @body=@msg, --內文

		  32: @query='select getdate()',  --還可以下查詢式哦

		  33: @body_format=HTML  --也可以使用HTML格式

		  34:  

		  35: FETCH NEXT FROM @MyCursor

		  36: INTO @titile,@mail,@msg

		  37:  

		  38: END

		  39:  

		  40: CLOSE @MyCursor

		  41: DEALLOCATE @MyCursor

		  42:  

執行後，sql server回傳訊息，表示我發了四封信..

在outlook看到的結果，因為我有設定htmlcode，所以字型有變大了

關於 db mail的設定，可以到下列網址有更詳細的設定

‧覺得文章不錯請給我一個『讚』作為鼓勵喔！