德瑞克：SQL Server 學習筆記: 建立與組態 Database Mail，使用Transact-SQL為例

2012-05-16

建立與組態 Database Mail，使用Transact-SQL為例

示範環境：SQL Server 2012 版本。

任務1. 設定啟用Database Mail功能：

-- 設定啟用Database Mail功能
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO

-- 查詢是否已經啟用Database Mail功能
SELECT name N'組態選項的名稱', value N'針對這個選項所設定的值', value_in_use N'這個選項目前有效的執行值',  description N'組態選項的描述'
FROM sys.configurations
WHERE name='Database Mail XPs'
GO

-- 01_啟用Database Mail功能

任務2：建立SMTP郵件帳戶：MIS99_Account

-- 建立SMTP郵件帳戶：MIS99_Account
USE master
GO
IF  EXISTS (SELECT name FROM msdb.dbo.sysmail_account WHERE name='MIS99_Account')
BEGIN
 EXEC msdb.dbo.sysmail_delete_account_sp @account_name = 'MIS99_Account' ;
END
GO
EXEC msdb.dbo.sysmail_add_account_sp 
 @account_name = N'MIS99_Account',  -- 加入的帳戶名稱
 @email_address = 'u99@mydba.com',  -- 網際網路電子郵件地址
 @display_name = 'MIS99',   -- 顯示名稱
 @replyto_address = 'u99@mydba.com', -- 回應的傳送地址
 @description = N'MIS99用的SMTP帳戶 ', -- 帳戶的描述
 @mailserver_name = '10.0.1.101',  -- SMTP 郵件伺服器的名稱或 IP 位址
 @port = 25,     -- 電子郵件伺服器的通訊埠編號。預設值是 25。
 @username = 'u99',    -- 登入電子郵件伺服器的使用者名稱
 @password = 'P@ssw0rd',   -- 登入電子郵件伺服器的密碼
 @use_default_credentials =0,   --  參數是 0，Database Mail 會傳送 @username 和 @password 參數
 @enable_ssl = 0    -- 是否使用安全通訊端層加密通訊。預設值是 0。
GO

-- 列出 Database Mail 帳戶的相關資訊 (密碼除外)。
EXEC msdb.dbo.sysmail_help_account_sp ;
GO

-- 02_列出 Database Mail 帳戶的相關資訊 (密碼除外)

任務3：建立新的 Database Mail 設定檔：MIS99_Account_Profile

-- 01_建立新的 Database Mail 設定檔：MIS99_Account_Profile
USE master
GO
IF  EXISTS (SELECT name FROM msdb.dbo.sysmail_profile WHERE name='MIS99_Account_Profile')
BEGIN
 EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = N'MIS99_Account_Profile';
END
EXEC msdb.dbo.sysmail_add_profile_sp
  @profile_name = N'MIS99_Account_Profile', -- 新設定檔的名稱
  @description = N'MIS99_Account 的設定檔' ; -- 新設定檔的選擇性描述
GO

-- 列出郵件設定檔的相關資訊。
EXEC msdb.dbo.sysmail_help_profile_sp;
GO

-- 02_將 Database Mail 帳戶加入 Database Mail 設定檔中。
EXEC msdb.dbo.sysmail_add_profileaccount_sp
 @profile_name = N'MIS99_Account_Profile',
 @account_name = N'MIS99_Account',
 @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'MIS99_Account_Profile',
 @account_name = N'MIS99_Account';
GO

-- 03_授與資料庫使用者或角色使用 Database Mail 設定檔的權限。
/*
主要參數說明：

(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
    @principal_name = 'public',     -- 設定為：公用設定檔
    @profile_name = N'MIS99_Account_Profile',
    @is_default = 1 ;
GO

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

-- 03_列出郵件設定檔的相關資訊

-- 04_列出與一個或多個 Database Mail 設定檔相關聯的帳戶

-- 05_列出 Database Mail 設定檔和資料庫主體間之關聯的相關資訊。

任務4：傳送測試郵件

-- 傳送測試郵件
USE master
GO
EXEC msdb.dbo.sp_send_dbmail 
 @recipients = 'u99@mydba.com',
 @body = N'這是電子郵件訊息的主體。',
 @subject =N'這是電子郵件訊息的主旨。如果未指定主旨，預設值便是「SQL Server 訊息」。' 
GO

任務5：查看與 Database Mail 有關的訊息

-- 查看：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

-- 06_查詢所有訊息的狀態