公告版位
哈囉,丫德我會常常更新一些工作上的心情記事及資訊科技新知與大家分享...

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

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

以下就來個範例介紹吧

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

image


image

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

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


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

image

接下來會出現精靈

image

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

image

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


image

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

image

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

image

輸入收件者的mail帳號

image

如果上述的測試是成功的,那就可以開始撰寫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回傳訊息,表示我發了四封信..

image

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

image


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

丫德 發表在 痞客邦 PIXNET 留言(3) 人氣()


留言列表 (3)

發表留言
  • 悄悄話
  • 小葉
  • Y德 您好:
    感謝您的分享,我有測試執行迴圈從資料庫抓資料再發送mail,
    可是我有10筆只寄出6筆其他的都沒收到,請問我有哪裡沒設定好?
    謝謝!!
  • 你好
    有幾個地方你可以check
    在執行ssis時使用中斷點的方式,例如變數改變時的當下,先停下來讓你看mail是否正確
    另外的檢查方式,例如收件者的isp是否把寄件者的mail歸到垃圾信去了
    再另一個就是從資料庫抓出來的mail是否為空值,是否是不正確的mail格式,而被你的smtp視為不正確的mail而沒有發送出去,可能可以檢查一下你的smtp的log

    以上你參考看看

    丫德 於 2012/01/18 11:14 回覆

  • 小葉
  • 您好:
    我剛剛又寄一次10封收到6封
    因為我10筆資料都是我自己,所以不會有mail格式問題,
    我檢查log檔,是說郵件伺服器失敗Too many smtp sessions for this host

    感謝回覆!!
  • 你的smtp是從?
    看來不是ssis的問題了,那是你的smtp端的設定問題,他有寄信限制,這個就要詢問你的smtp提供者了,已不在這個主題的討論範圍了哦!

    丫德 於 2012/01/18 11:48 回覆