Theme NexT works best with JavaScript enabled

ShunNien's Blog

不積跬步，無以致千里；不積小流，無以成江海。

0%

Sending DataBase Mail in Sql Server

發表於 更新於 分類於 Disqus：
使用 Sql Server 的 Database Mail 來發送郵件

緣起

剛好有個需要發送郵件的需求，而且不是對外服務的需求，就拿資料庫來發送了。

我的環境

中文版

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
$ systeminfo | findstr /B /C:"作業系統名稱" /C:"作業系統版本" /c:"作業系統設定" /c:"作業系統組建類型" /c:"系統類型" /c:"處理器" /c:"BIOS" /c:"實體記憶體總計" /c:"虛擬記憶體"
作業系統名稱:         Microsoft Windows 10 專業版
作業系統版本:         10.0.10240 N/A 組建 10240
作業系統設定:         獨立工作站
作業系統組建類型:     Multiprocessor Free
系統類型:             x64-based PC
處理器:               已安裝 1 處理器。
BIOS 版本:            American Megatrends Inc. 219, 2015/5/4
實體記憶體總計:       16,264 MB
虛擬記憶體: 大小上限: 18,696 MB
虛擬記憶體: 可用:     8,883 MB
虛擬記憶體: 使用中:   9,813 MB

$ sqlcmd -Q "SELECT @@VERSION"

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
        May  4 2015 19:11:32
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 10240: ) (Hypervisor)

c:\Windows\Microsoft.NET\Framework\v4.0.30319
$ csc
Microsoft (R) Visual C# Compiler version 4.6.0079.0
for C# 5
Copyright (C) Microsoft Corporation. All rights reserved.

This compiler is provided as part of the Microsoft (R) .NET Framework, but only supports language versions up to C# 5, which is no longer the latest version. For compilers that support newer versions of the C# programming language, see http://go.microsoft.com/fwlink/?LinkID=533240

英文版

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
systeminfo | findstr /b /c:"OS Name" /c:"OS Version" /c:"OS Manufacturer" /c:"OS Configuration" /c:"OS Build Type" /c:"System Model" /c:"System Type" /c:"Processor" /c:"BIOS" /c:"Total Physical Memory" /c:"Virtual Memory"

OS Name:                   Microsoft Windows Server 2012 R2 Standard
OS Version:                6.3.9600 N/A Build 9600
OS Manufacturer:           Microsoft Corporation
OS Configuration:          Member Server
OS Build Type:             Multiprocessor Free
System Model:              VMware Virtual Platform
System Type:               x64-based PC
Processor(s):              2 Processor(s) Installed.
BIOS Version:              Phoenix Technologies LTD 6.00, 4/14/2014
Total Physical Memory:     4,096 MB
Virtual Memory: Max Size:  4,800 MB
Virtual Memory: Available: 2,497 MB
Virtual Memory: In Use:    2,303 MB


C:\>sqlcmd -Q "SELECT @@VERSION"

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
        May 14 2014 18:34:29
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hype
rvisor)

(1 rows affected)

Sql Server Database Mail

設定方式有兩種，一種是透過 T-SQL，另一種是透過 GUI 介面設定(只需要滑鼠操作就可以完成)

  • Transact-SQL
  • Database Mail 組態精靈

Transact-SQL

- 啟用 Database Mail

首先檢查是否已經啟用 Database Mail，沒有啟用的話，啟用它。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 啟用 Database Mail 必須在 master 系統資料庫下
Use master
GO

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO

sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

-- 檢查是否啟用成功
SELECT
	name,
	value,
	description
FROM sys.configurations
WHERE name = 'Database Mail XPs'

- 建立 Database Mail 帳戶

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 檢查是否有 Demo 這個帳戶，有的話就刪除 
IF  EXISTS (SELECT name FROM msdb.dbo.sysmail_account WHERE name='Demo')
BEGIN
 EXEC msdb.dbo.sysmail_delete_account_sp @account_name = 'Demo' ;
END

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Demo',  -- Database Mail 帳戶名稱
@email_address = 'wiamyu@gmail.com',  -- 電子郵件位址(寄件者Email)
@display_name = 'ShunNien',  -- 顯示的名稱
@replyto_address = Null,  -- 回應的傳送地址
@description = 'Database Mail Account',  -- 帳戶的描述

-- SMTP 郵件伺服器的名稱或 IP 位址
-- 127.0.0.1 是本機端位址，配合 smtp4dev
@mailserver_name = '127.0.0.1' ,
@mailserver_type =  'SMTP' ,  -- 郵件伺服器的類型
@port =  25 ,  -- 郵件伺服器的通訊埠編號。預設值是 25
@username =  Null,  -- 登入電子郵件伺服器的使用者名稱
@password =  Null,  -- 登入電子郵件伺服器的密碼

-- 參數 0 會使用 @username、@password 這兩個參數
-- 參數 1 會使用 資料庫的驗證，預設是參數 0
@use_default_credentials=0,
@enable_ssl=0  -- 是否使用SSL(安全通訊端層加密通訊)。預設值是 0 

-- 列出 Database Mail 帳戶資訊(密碼不會顯示)
EXEC msdb.dbo.sysmail_help_account_sp
GO

- 建立 Database Mail Profile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 檢查是否存在 SendMail 這名稱的設定檔，有的話則刪除
IF  EXISTS (SELECT name FROM msdb.dbo.sysmail_profile WHERE name='SendMail')
BEGIN
 EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = N'SendMail';
END
-- 建立新的 Database Mail 設定檔
EXEC msdb.dbo.sysmail_add_profile_sp
  @profile_name = 'SendMail', -- 新設定檔的名稱
  @description = '' ; -- 新設定檔的性描述
GO
 
-- 列出郵件設定檔的資訊。
EXEC msdb.dbo.sysmail_help_profile_sp;
GO

- 把帳戶加入到設定檔

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
EXEC msdb.dbo.sysmail_add_profileaccount_sp
 @profile_name = N'SendMail',  -- 設定檔名稱
 @account_name = N'Demo',  -- 帳戶名稱
 -- 帳戶在設定檔內的序號。沒有預設值。序號決定了帳戶在設定檔中的使用順序。
 @sequence_number =1 ;
GO

-- 列出 Database Mail 設定檔相關聯的帳戶
EXEC msdb.dbo.sysmail_help_profileaccount_sp
GO

-- 補充資料 
-- 若是要從 Database Mail 設定檔中移除帳戶。
EXEC msdb.dbo.sysmail_delete_profileaccount_sp
 @profile_name = N'SendMail',
 @account_name = N'Demo';
GO

- 設定使用 Database Mail 設定檔的權限

此篇在德瑞克大大的文章說明得很詳細

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
主要參數說明： 
(1) @principal_name：
這是關聯的 msdb 資料庫中，資料庫使用者或角色的名稱。 
principal_name 是 sysname，預設值是 NULL。
 
您必須指定 principal_id 或 principal_name 其中之一。 
如果 principal_name 是 'public'，這個設定檔會成為公用設定檔，會將存取權授與資料庫中的所有主體。
 
(2) @is_default
指定這個設定檔是否為主體的預設設定檔。 主體只能有一個預設設定檔。
*/
EXEC msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = N'SendMail',
    @principal_name = 'public',     -- 設定為：公用設定檔
    @is_default = 1 ;
GO

-- 列出 Database Mail 設定檔和資料庫主體間之關聯的相關資訊。
EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
GO

- 測試發送

到這邊設定就完成了，可以檢查一下設定檔

1
2
3
4
-- 查看 Database Mail 帳戶
SELECT * FROM msdb.dbo.sysmail_account
-- 查看設定檔
SELECT * FROM msdb.dbo.sysmail_profile

接著進行發送 mail 測試，配合smtp4dev，就可以在本機端接收 mail 了，設定可以參考 demo 大的這篇

1
2
3
4
5
EXEC msdb.dbo.sp_send_dbmail   
@profile_name='SendMail',  
@recipients='test@gmail.com',  
@subject= N'這是郵件主旨',  
@body=N'這是郵件內容'

Send Mail Test

- 測試發送

查看發送的狀況

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- 查看：Database Mail 處理的所有訊息
SELECT * FROM msdb.dbo.sysmail_allitems
GO
 
-- 查看：傳送成功的訊息時
SELECT * FROM msdb.dbo.sysmail_sentitems
GO
 
-- 查看：哪些訊息未成功傳送
SELECT * FROM msdb.dbo.sysmail_faileditems
GO
 
-- 查看：未傳送或正在重試狀態的 Database Mail 訊息
SELECT * FROM msdb.dbo.sysmail_faileditems
GO
 
-- 查看：Database Mail 系統傳回的錯誤訊息之類
SELECT * FROM msdb.dbo.sysmail_event_log
GO
 
-- 查看：Database Mail 附加檔案的相關資訊
SELECT * FROM msdb.dbo.sysmail_mailattachments
GO

-- 發送郵件的狀態
SELECT
	last_mod_date,
	CASE sent_status
		WHEN 1 THEN 'Sent Successfully'
		WHEN 2 THEN 'Failed'
		WHEN 3 THEN 'Unsent'
	END sent_status,
	[subject],
	recipients
FROM
	msdb.dbo.sysmail_mailitems
ORDER BY last_mod_date DESC

-- 發送失敗的郵件與錯誤訊息
SELECT
	profile_id,
	items.last_mod_date,
	items.[subject],
	l.[description] ErrorMessage,
	items.sent_status,
	recipients
FROM
	msdb.dbo.sysmail_faileditems AS items
	INNER JOIN msdb.dbo.sysmail_event_log AS l
		ON items.mailitem_id = l.mailitem_id
ORDER BY last_mod_date DESC

Database Mail 組態精靈

首先開啟SSMS，接著選擇管理，然後選擇Database Mail，在選項上按下滑鼠右鍵
SSMS Step1-1 En
SSMS Step1-2 En
SSMS Step1 Cht
啟動精靈後，直接下一步
SSMS Step2 Configuration Wizard En
SSMS Step2 Cht
接著輸入設定檔資訊
SSMS Step3 設定檔資訊 Cht
新增 Database Mail 帳戶資料
SSMS Step4 新增 Database Mail 帳戶 Cht
設定帳戶資訊，接著將帳戶加入到設定檔，並進行下一步
SSMS Step5 將帳戶加入到設定檔 Cht
勾選設定檔
SSMS Step6 管理設定檔安全性 Cht
設定系統參數可以不用變動，直接點擊下一步直到完成
SSMS Step7 設定系統參數 Cht
設定完成後，點擊關閉
SSMS Step8 組態精靈設定 Cht
這樣就設定完成了，需要注意的是，登入的帳戶權限記得開啟系統資料庫
Security Set01 Security Properties En
Security Set02 Login Properties En

參考資料

歡迎關注我的其它發布渠道