搜尋本站文章

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_查詢所有訊息的狀態






-- 07_組態精靈_管理現有的帳戶



-- 08_組態精靈_管理現有的設定檔



-- 09_組態精靈_管理設定檔安全性







參考資料

實作練習:建立 Database Mail 與 SQL Agent Mail,以 SQL Server 2008 為例
http://sharedderrick.blogspot.com/2011/01/database-mail-sql-agent-mail-sql-server.html

SQL Server 2008 介面區組態(Surface Area Configuration,SAC):啟用 xp_cmdshell
http://sharedderrick.blogspot.com/2009/04/sql-server-2008-surface-area.html

--
建立 Database Mail 設定檔
http://msdn.microsoft.com/zh-tw/library/ms187605.aspx

建立 Database Mail 帳戶
http://msdn.microsoft.com/zh-tw/library/ms190658.aspx

設定 Database Mail
http://msdn.microsoft.com/zh-tw/library/hh245116.aspx

sysmail_add_account_sp (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms182804.aspx

sysmail_add_profile_sp (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms188058.aspx

Database Mail 檢視 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms188023

--
Gmail 外寄郵件 (SMTP) 伺服器 - 需要 TLS2 或安全資料傳輸層 (SSL):
http://support.google.com/mail/bin/answer.py?hl=zh-Hant&answer=13287

How to configure SQL Server Database Mail to send email using your Windows Live Mail Account or your GMail Account
http://blogs.msdn.com/b/suhde/archive/2009/07/12/how-to-configure-sql-server-database-mail-to-send-email-using-your-windows-live-mail-account-or-your-gmail-account.aspx

Setup SQL Server Database Mail to use a Gmail, Hotmail, Yahoo or AOL account
http://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/