SQL Server 設定資料庫郵件功能

下面的指令碼示範，在 SQL Server 2005/2008 中，使用 Gmail 帳號設定資料庫郵件功能

 

-- =================================================

-- 啟用資料庫郵件功能

-- =================================================

EXEC sys.sp_configure N'show advanced options', 1

RECONFIGURE;

 

EXEC sys.sp_configure 'Database Mail XPs', 1;

RECONFIGURE;

 

 

-- =================================================

-- 配置

-- =================================================

DECLARE

    @account_name sysname,

    @profile_name sysname;

SELECT

    @account_name = N'GmailTest@gmail.com',  -- 資料庫郵件帳號

    @profile_name = N'gmail';                -- 資料庫郵件設定檔名

 

-- 資料庫郵件帳號

EXEC msdb.dbo.sysmail_add_account_sp

    @account_name = @account_name,

    @email_address = N'GmailTest@gmail.com',  -- Email 地址

    @display_name = N'gmail test',            -- 回複地址

    @mailserver_name = N'smtp.gmail.com',     -- Gmail smtp 伺服器位址

    @port = 25,                               -- 連接埠號碼(對於Gmail, 如果 25 不通則改用 456)

    @username = N'GmailTest@gmail.com',       -- Gmail 郵件地址

    @password = N'abc.123',                   -- Gmail 郵件帳號密碼

    @use_default_credentials = 0,

    @enable_ssl = 1;                          -- 啟用SSL 通訊加密

 

-- 資料庫郵件設定檔

EXEC msdb.dbo.sysmail_add_profile_sp

    @profile_name = @profile_name;

   

-- 把資料庫郵件帳號添加到郵件設定檔中

EXEC msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = @profile_name,

    @account_name = @account_name,

    @sequence_number = 1;

 

-- 授予任意使用者使用資料庫郵件設定檔的許可權

EXEC msdb.dbo.sysmail_add_principalprofile_sp

    @principal_name = N'guest',

    @profile_name = @profile_name,

    @is_default = 0;

 

 

-- =================================================

-- 發送郵件

-- =================================================

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = @profile_name,

    @recipients = N'GmailTest@gmail.com',      -- 收件者地址

    @subject = N'test mail from database',     -- 郵件主題

    @body = N'this is a test mail';            -- 郵件內容

 

--6. 查看郵件發送情況：

use msdb

go

select * from sysmail_allitems

select * from sysmail_mailitems

select * from sysmail_event_log

 

-- =================================================

-- 刪除配置

-- =================================================

/* --

DECLARE

    @account_name sysname,

    @profile_name sysname;

SELECT

    @account_name = N'GmailTest@gmail.com',

    @profile_name = N'gmail';

 

-- 從郵件設定檔中刪除資料庫郵件帳號

EXEC msdb.dbo.sysmail_delete_profileaccount_sp

    @profile_name = @profile_name,

    @account_name = @account_name;

   

-- 刪除資料庫郵件帳號

EXEC msdb.dbo.sysmail_delete_account_sp

    @account_name = @account_name;

 

-- 刪除資料庫郵件設定檔

EXEC msdb.dbo.sysmail_delete_profile_sp

    @profile_name = @profile_name;

 

 

-- 禁用資料庫郵件功能

EXEC sys.sp_configure 'Database Mail XPs', 0;

RECONFIGURE;

 

EXEC sys.sp_configure N'show advanced options', 0

RECONFIGURE;

-- */

 

'

-- =================================================

-- 發送附件

-- =================================================

 

通過電子郵件傳送檔案附件的能力對工作效率來說是十分重要的。下面的指令碼將會發送一封電子郵件，其中包含檔案名稱為FileAttachment.txt的附件，給附件儲存於我的C盤磁碟機上。

你需要確保檔案的存在，這樣發送過程就不會發生錯誤。

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body='Message Body',
@subject ='Message Subject',
@profile_name ='Database-mailProfile',
@file_attachments ='C://FileAttachment.txt';

-- =================================================

-- 敏感度和重要性

-- =================================================

通常需要將一封郵件標記為敏感或重要，以使得收件者知道這些資訊需要小心保護。下面的指令碼將會發送一封郵件，敏感度為私人，重要性為進階。此外，指令碼將會複製郵件地址yourname@yourdomain.com到信件中。

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body='Message Body',
@sensitivity ='Personal',
@importance ='High',
@copy_recipients ='chapman.tim@gmail.com',
@subject ='Message Subject',
@profile_name ='Database-mailProfile';

-- =================================================

-- 查詢結果

-- =================================================

擁有電子郵件發送能力的資料庫引擎的一個重要特徵是它可以讓你向發送過程傳遞一個查詢。在SQL Mail和Database Mail中都有該特性。Database Mail中的一個新特性是可以將查詢結果作為附件，這一點在下面的指令碼中展示。如果你不將查詢結果作為一檔案，那麼它將被放置在郵件內文中。見列表B。

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'chapman.tim@gmail.com',
@body='Message Body',

@subject ='Message Subject',
@profile_name ='DatabaseMailProfile',
@query ='SELECT Product FROM sb2..SalesHistory GROUP BY Product HAVING COUNT(*) > 3',

@attach_query_result_as_file = 1,
@query_attachment_filename ='Results.txt'

HTML格式的信件

在資料庫郵件中我最喜愛的新特徵是可以將你的郵件格式化為HTML格式。簡單看來，這沒什麼大不了的，但是這確實帶來一些很好的可能性。在前面的例子中，你發送的查詢結果包含在郵件內文中，因為該例子只包含一列資料，所以看起來沒有什麼不方便的。

但是，當在查詢結果集中包含多個域時，格式化變得每況愈下。一個好訊息是，可以很簡單的使用SQL Server 2005中的一些XML和HTML新特徵來格式化你的查詢結果集，因此，可以很容易的在信件中查看查詢結果。列表C中的指令碼示範了如何利用查詢結果發送一封XML/HTML格式的電子郵件。

DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT Product AS 'td','',SUM(SalePrice) AS 'td'
FROM SalesHistory GROUP BY Product FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1>Sales Reports</H1><body bgcolor=yellow><table border = 2><tr><th>Product</th><th>SaleAmount</th></tr>' SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'chapman.tim@gmail.com',@body = @body,@body_format ='HTML',@subject ='Message Subject',@profile_name ='DatabaseMailProfile'當你發送HTML格式的郵件時，允許你將查詢結果嵌入到郵件的查詢部分，從而可以將查詢結果格式化成為更容易看的形式。（雖然，你可能會爭論說我在信件中使用的黃色背景，看起來並不是很舒服。

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/fallingstarNo1/archive/2009/11/22/4853061.aspx