信德隨想

在以往發送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真是好物..

---

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
--exec SendMail
--
Create PROCEDURE [dbo].[SendMail] as
DECLARE @mail varchar(50)
DECLARE @msg varchar(255)
DECLARE @titile varchar(255)
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD

FOR
select name,mail,bodymsg from mail
OPEN @MyCursor

--取得第一筆的值放到變數
FETCH NEXT FROM @MyCursor
INTO @titile,@mail,@msg
WHILE @@FETCH_STATUS = 0

BEGIN
set @msg='<B><font size=7>'+@msg+'</B></font>'
exec msdb.dbo.sp_send_dbmail
@profile_name='test',   --設定檔
@recipients=@mail, --收件者
@subject=@titile,  --主旨

@body=@msg, --內文
@query='select getdate()',  --還可以下查詢式哦
@body_format=HTML  --也可以使用HTML格式

FETCH NEXT FROM @MyCursor
INTO @titile,@mail,@msg

END

CLOSE @MyCursor
DEALLOCATE @MyCursor

---

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

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

關於 db mail的設定，可以到下列網址有更詳細的設定
http://msdn.microsoft.com/zh-tw/library/ms190307.aspx