這只是筆記本

— 啟動 Database Mail
exec sp_configure ‘show advanced options’, 1
reconfigure
exec sp_configure ‘Database Mail XPs’, 1
reconfigure
exec msdb.dbo.sysmail_start_sp;

— 停止 Database Mail
exec sp_configure ‘show advanced options’, 1
reconfigure
exec sp_configure ‘Database Mail XPs’, 1
reconfigure
exec msdb.dbo.sysmail_stop_sp;

— 建立郵件帳號
exec msdb.dbo.sysmail_add_account_sp
@account_name = ‘Your Account’,
@description = ‘郵件帳號’,
@email_address = ‘aaa@email.com’, –收件者
@replyto_address = ‘bbb@email.com’, –回覆信箱
@display_name = ‘顯示名稱’,
@mailserver_name = ‘mymail.exchange.com’,
@port = 587,
@username = ‘ccc@email.com’,
@password = ‘PWD’,
@enable_ssl = 1

— 刪除郵件帳號
exec msdb.dbo.sysmail_delete_account_sp @account_name = ‘Your Account’

— 建立設定檔
exec msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘Your Profile’,
@description = ‘郵件設定檔’

— 刪除設定檔
exec msdb.dbo.sysmail_delete_profile_sp @profile_name = ‘Your Profile’

— 建立 Profile 與 Account 的連接
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘Your Profile’,
@account_name = ‘Your Account’,
@sequence_number = 1

— 授予 DBMailUsers 存取設定檔的權限
exec msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘Your Profile’,
@principal_name = ‘public’, — 設定為公開
@is_default = 1

— 刪除 Profile 與 Account 的連接
exec msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = ‘Your Profile’,
@account_name = ‘Your Account’

— 發送 Email
exec msdb.dbo.sp_send_dbmail
@recipients = N’yyy@email.com’,
@body = ‘這是測試信件’,
@subject = ‘TEST’,
@body_format = ‘HTML’, — 郵件格式為HTML
@profile_name = ‘Your Profile’,
@copy_recipients = ‘rrr@email.com’ — 副本

— Profiles 狀態
select * from msdb.dbo.sysmail_profile

— Accounts 狀態
select * from msdb.dbo.sysmail_account

— Profile & Accounts 連接狀態
select * from msdb.dbo.sysmail_profileaccount

— Principal Profile 狀態
select * from msdb.dbo.sysmail_principalprofile

— Mail Server 狀態
select * from msdb.dbo.sysmail_server
select * from msdb.dbo.sysmail_servertype
select * from msdb.dbo.sysmail_configuration

— 檢查 Email 發送狀態 顯示全部
select * from msdb.dbo.sysmail_allitems
— 檢查 Email 發送狀態 已送出
select * from msdb.dbo.sysmail_sentitems
— 檢查 Email 發送狀態 尚未送出
select * from msdb.dbo.sysmail_unsentitems
— 檢查 Email 發送狀態 發送失敗
select * from msdb.dbo.sysmail_faileditems

— 刪除 LOG 紀錄：
exec msdb.dbo.sysmail_delete_log_sp @event_type = ‘success’
success：刪除成功紀錄。
warning：刪除警告紀錄。
error：刪除錯誤成功紀錄。
information：刪除資訊紀錄。

— 刪除郵件：
exec msdb.dbo.sysmail_delete_mailitems_sp @sent_status = ‘sent’
sent：刪除已成功發送的郵件。
retrying：刪除正在重試的郵件。
unsent：刪除尚未發送的郵件。
failed：刪除發送失敗的郵件。