DB Mail

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

EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;


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

-- 建立SMTP郵件帳戶：DBMail
USE master
GO
IF EXISTS (SELECT name FROM msdb.dbo.sysmail_account WHERE name='DBMail')
BEGIN
EXEC msdb.dbo.sysmail_delete_account_sp @account_name = 'DBMail' ;
END

EXEC msdb.dbo.sysmail_add_account_sp
@account_name = N'DBMail', -- 加入的帳戶名稱
@email_address = 'account@server.com.tw', -- 網際網路電子郵件地址
@display_name = 'DBMAIL', -- 顯示名稱
@replyto_address = 'account@server.com.tw', -- 回應的傳送地址
@description = N'DB Mail', -- 帳戶的描述
@mailserver_name = '127.0.0.1', -- SMTP 郵件伺服器的名稱或 IP 位址
@port = 25, -- 電子郵件伺服器通訊埠編號。預設值是 25。
@username = 'mail.account', -- 登入電子郵件伺服器的使用者名稱
@password = 'password', -- 登入電子郵件伺服器的密碼
@use_default_credentials = 0, -- 參數是0，DB Mail會傳送@username和@password。
@enable_ssl = 0 -- 是否使用安全通訊端層加密通訊。預設值是 0。

EXEC msdb.dbo.sysmail_help_account_sp ;

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

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

-- 02_將 Database Mail 帳戶加入 Database Mail 設定檔中。
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = N'DBMail_Profile',
@account_name = N'DBMail',
@sequence_number =1;-- 帳戶在設定檔內的序號。沒有預設值。決定帳戶在設定檔中的使用順序。

-- 列出與一個或多個 Database Mail 設定檔相關聯的帳戶。
EXEC msdb.dbo.sysmail_help_profileaccount_sp

-- 若是要從 Database Mail 設定檔中移除帳戶。
EXEC msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = N'DBMail_Profile',
@account_name = N'DBMail';

-- 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'DBMail_Profile',
@is_default = 1 ;

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

-- Send DB mail sp
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail_Profile',
@recipients = 'mail.account@mailserver.com.tw',
@copy_recipients = '',
@blind_copy_recipients = '',
@importance = 'LOW', -- LOW, NORMAL, HIGH
@sensitivity = 'Normal',-- Normal,Personal,Private,Confidential
@body_format = 'TXT', -- TXT, HTML
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message'

-- 查看：Database Mail 處理的所有訊息
SELECT * FROM msdb.dbo.sysmail_allitems

-- 查看：傳送成功的訊息時
SELECT * FROM msdb.dbo.sysmail_sentitems

-- 查看：哪些訊息未成功傳送
SELECT * FROM msdb.dbo.sysmail_faileditems

-- 查看：未傳送或正在重試狀態的 Database Mail 訊息
SELECT * FROM msdb.dbo.sysmail_faileditems

-- 查看：Database Mail 系統傳回的錯誤訊息之類
SELECT * FROM msdb.dbo.sysmail_event_log

-- 查看：Database Mail 附加檔案的相關資訊
SELECT * FROM msdb.dbo.sysmail_mailattachments

-- 參考
-- <德瑞克：SQL Server 學習筆記>