Leon的程式心得

首先要透過SSMS做一些設定，其方法如下:

1.在物件總管中的[管理]，找到[DataBase Mail]節點，按右鍵選擇[設定DataBase Mail]

2.進入組態精靈

 

3.選取第一項新增設定

 

4.若沒有啟用DataBase Mail功能，在這裡選擇[是]啟用功能

 

5.進入設定檔頁面，先按加入設定帳戶

 

6.設定屬性

7.按確定後，SMTP帳戶會新增一欄

上面的設定檔名稱與描述也要輸入

這裡要記一下設定檔名稱，等一下會用到

 

8.按下一步，進到管理設定檔安全性，這裡就先勾選公用並按下一步

 

9.這裡可以設定一些系統參數，點選左欄的參數，下方會顯示描述

而右邊可以進行參數的修改

 

10.按完成後，就可以看到設定結果

 

11.接下來對[DataBase Mail]節點，按右鍵選擇[傳送測試電子郵件]

輸入測試信的收件者信箱後，點選傳送測試電子郵件

 

12.此時可以到信箱看有沒有收到信件

若沒收到信件，可以對[DataBase Mail]節點，按右鍵選擇[檢視DataBase Mail記錄]

裡面會列出Log，可以查看訊息來解決問題

通常無法寄出的原因有兩種

(1)加入SMTP帳戶的設定值沒設定好

這裡可以再按一次[設定DataBase Mail]，選擇[管理DataBase Mail帳戶和設定檔]

選擇[檢視、變更或刪除現有的帳戶]來進行修改

(2)低安全性應用程式存取權

若是Log出現以下訊息

郵件伺服器失敗，所以無法將郵件傳送給收件者。

(使用帳戶 1 (2021-01-07T13:34:06) 傳送郵件。例外狀況訊息: 無法傳送郵件到郵件伺服器。

(SMTP 伺服器需要安全連接，或用戶端未經驗證。 伺服器回應為: 5.7.0 Authentication Required. Learn more at)。)

或者發信人信箱出現以下信件

這是因為Google對於低安全性應用程式存取權的預設值為關閉

所以只要開啟允許權限，就能順利寄出信件

設定路徑為:右上方的Google應用程式-->Google帳戶-->左邊的安全性

往下拉找到低安全性應用程式存取權

點進去後開啟即可

收到信件後，代表設定已完成，之後就可以透過MSSQL發送信件了

 

另外補充一點

若想看跟DataBase Mail相關的資料的話

可以進到物件總管中的[資料庫]-->[系統資料庫]-->[msdb]-->[資料表]-->[系統資料表]

找到dbo.sysmail開頭的table

像設定檔可以查看dbo.sysmail_profile

Log可以查看dbo.sysmail_log

 

 

接下來講解如何透過SQL語法寄出信件

主要的核心是透過SSMS內建的Stored Procedured來發信

SP的名稱為msdb.dbo.sp_send_dbmail

有興趣知道實作內容的話

可以進到物件總管中的[資料庫]-->[系統資料庫]-->[msdb]-->[可程式性]-->[預存程序]-->[系統預存程序]

裡面找到msdb.dbo.sp_send_dbmail這個SP

而一般會建立自己的SP去執行sp_send_dbmail

建立好SP後直接執行就可以寄出信了

以下為參考範例SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE SendMail AS
DECLARE @mail varchar(50)
DECLARE @msg varchar(1000)
DECLARE @title varchar(1000)
--下面五個是測試用table--Member的欄位
DECLARE @ID varchar(50)
DECLARE @Name varchar(50)
DECLARE @Account varchar(50)
DECLARE @Password varchar(50)
DECLARE @Email varchar(50)

--定義CURSOR取得資料
DECLARE @cur CURSOR
SET @cur = CURSOR FAST_FORWARD
FOR
select * from Member
OPEN @cur

FETCH NEXT FROM @cur
INTO @ID,@Name,@Account,@Password,@Email
WHILE @@FETCH_STATUS = 0
BEGIN

    --建立信件內容主體
    set @msg='ID:' + @ID + '，Name:' + @Name + '，Account:' + @Account + '，Password:' + @Password + '，Email' + @Email

    --透過執行sp_send_dbmail寄出信件
    exec msdb.dbo.sp_send_dbmail
    @profile_name='test',                --設定檔名稱(剛剛要讀者記住的名稱)
    @recipients='***@***',               --收件者
    --@copy_recipients='***@***',        --副本
    --@blind_copy_recipients='***@***',  --密件副本
    @subject='SQLServer測試',             --主旨
    @body=@msg,                          --內容
    --@query='select getdate()',         --可以下SQL
    --@file_attachments='C:\test.txt',   --附件
    --@attach_query_result_as_file=1,    --把query結果設為附件，若不設定就會出現在mail內容中
    @body_format=TEXT                    --內容使用text格式
    --@body_format=HTML'                 --內容使用HTML格式

    FETCH NEXT FROM @cur
    INTO @ID,@Name,@Account,@Password,@Email

END

CLOSE @cur
DEALLOCATE @cur
GO