sp_send_dbmail (Transact-SQL)

更新: 2005 年 12 月 5 日

將電子郵件訊息傳送給指定的收件者。訊息可能包含查詢結果集、檔案附件,或兩者皆有。當郵件順利放在 Database Mail 佇列時,sp_send_dbmail 會傳回訊息的 mailitem_id。這個預存程序在 msdb 資料庫中。

主題連結圖示Transact-SQL 語法慣例

語法

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
    [ , [ @subject = ] 'subject' ] 
    [ , [ @body = ] 'body' ] 
    [ , [ @body_format = ] 'body_format' ]
    [ , [ @importance = ] 'importance' ]
    [ , [ @sensitivity = ] 'sensitivity' ]
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
    [ , [ @query = ] 'query' ]
    [ , [ @execute_query_database = ] 'execute_query_database' ]
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
    [ , [ @query_attachment_filename = ] query_attachment_filename ]
    [ , [ @query_result_header = ] query_result_header ]
    [ , [ @query_result_width = ] query_result_width ]
    [ , [ @query_result_separator = ] 'query_result_separator' ]
    [ , [ @exclude_query_output = ] exclude_query_output ]
    [ , [ @append_query_error = ] append_query_error ]
    [ , [ @query_no_truncate = ] query_no_truncate ]
    [ , [ @query_result_no_padding = ] query_result_no_padding ]
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

引數

  • [ **@profile\_name=** ] 'profile_name'
    這是傳送訊息的來源設定檔名稱。profile_name 的類型是 sysname,預設值是 NULL。profile_name 必須是現有 Database Mail 設定檔的名稱。當沒有指定 profile_name 時,sp_send_dbmail 會使用目前使用者的預設私人設定檔。如果使用者沒有預設私人設定檔,sp_send_dbmail 會使用 msdb 資料庫的預設公用設定檔。如果使用者沒有預設私人設定檔,而資料庫也沒有預設公用設定檔,就必須指定 **@profile\_name**。
  • [ **@recipients=** ] 'recipients'
    這是訊息所要送往的電子郵件地址清單,用分號分隔各個電子郵件地址。收件者清單的類型是 varchar(max)。雖然這個參數是選擇性的,但至少必須指定 **@recipients**、**@copy\_recipients** 或 **@blind\_copy\_recipients** 其中之一,否則 sp_send_dbmail 會傳回錯誤。
  • [ **@copy\_recipients=** ] 'copy_recipients'
    這是訊息副本所要送往的電子郵件地址清單,用分號分隔各個電子郵件地址。副本收件者清單的類型是 varchar(max)。雖然這個參數是選擇性的,但至少必須指定 **@recipients**、**@copy\_recipients** 或 **@blind\_copy\_recipients** 其中之一,否則 sp_send_dbmail 會傳回錯誤。
  • [ **@blind\_copy\_recipients=** ] 'blind_copy_recipients'
    這是訊息密件副本所要送往的電子郵件地址清單,用分號分隔各個電子郵件地址。密件副本收件者清單的類型是 varchar(max)。雖然這個參數是選擇性的,但至少必須指定 **@recipients**、**@copy\_recipients** 或 **@blind\_copy\_recipients** 其中之一,否則 sp_send_dbmail 會傳回錯誤。
  • [ **@subject=** ] 'subject'
    這是電子郵件訊息的主旨。主旨的類型是 nvarchar(255)。如果未指定主旨,預設值便是「SQL Server 訊息」。
  • [ **@body=** ] 'body'
    這是電子郵件訊息的主體。訊息主體的類型是 nvarchar(max),預設值是 NULL。
  • [ **@body\_format=** ] 'body_format'
    這是訊息主體的格式。參數的類型是 varchar(20),預設值是 NULL。當指定這個選項時,會設定外寄訊息的標頭來表示訊息主體有指定的格式。參數可包含下列各值之一:

    • TEXT
    • HTML

    預設值是 TEXT。

  • [ **@importance=** ] 'importance'
    這是訊息的重要性。參數的類型是 varchar(6)。參數可包含下列各值之一:

    • Low
    • Normal
    • High

    預設值是 Normal。

  • [ **@sensitivity=** ] 'sensitivity'
    這是訊息的敏感性。參數的類型是 varchar(12)。參數可包含下列各值之一:

    • Normal
    • Personal
    • Private
    • Confidential

    預設值是 Normal。

  • [ **@file\_attachments=** ] 'file_attachments'
    這是附加至電子郵件訊息中的檔案名稱清單,用分號分隔各檔案名稱。清單中的檔案必須指定為絕對路徑。附件清單的類型是 nvarchar(max)
  • [ **@query=** ] 'query'
    這是要執行的查詢。查詢的結果可以附加成一個檔案,也可以包含在電子郵件訊息的主體中。查詢的類型是 nvarchar(max),且可以包含任何有效的 Transact-SQL 陳述式。請注意,查詢是在不同的工作階段中執行,因此,查詢無法使用呼叫 sp_send_dbmail 的指令碼中的本機變數。
  • [ **@execute\_query\_database=** ] 'execute_query_database'
    這是預存程序執行查詢所在的資料庫內容。參數的類型是 sysname,預設值是目前的資料庫。只有在指定 **@query** 時,這個參數才適用。
  • [ **@attach\_query\_result\_as\_file=** ] attach_query_result_as_file
    指定是否以附加檔案的方式傳回查詢的結果集。attach_query_result_as_file 的類型是 bit,預設值是 0。

    當值是 0 時,查詢結果會包含在電子郵件訊息的主體中,在 **@body** 參數的內容之後。當值是 1 時,會以附加檔案的方式傳回結果。只有在指定 **@query** 時,這個參數才適用。

  • [ **@query\_attachment\_filename=** ] query_attachment_filename
    指定查詢附加檔案結果集使用的檔案名稱。query_attachment_filename 的類型是 nvarchar(255),預設值是 NULL。當 attach_query_result 是 0 時,會忽略這個參數。當 attach_query_result 是 1 且這個參數是 NULL 時,Database Mail 會建立任意檔案名稱。
  • [ **@query\_result\_header=** ] query_result_header
    指定查詢結果是否包含資料行標頭。query_result_header 的類型是 bit。當值是 1 時,查詢結果會包含資料行標頭。當值是 0 時,查詢結果不會包含資料行標頭。這個參數的預設值是 1。只有在指定 **@query** 時,這個參數才適用。
  • [ **@query\_result\_width** = ] query_result_width
    這是以字元為單位的行寬,用來格式化查詢的結果。query_result_width 的類型是 int,預設值是 256。提供的值必須介於 10 到 32767 之間。只有在指定 **@query** 時,這個參數才適用。
  • [ **@query\_result\_separator=** ] 'query_result_separator'
    這是在查詢輸出中用來分隔資料行的字元。分隔符號的類型是 char(1)。預設值是 ' ' (空白)。
  • [ **@exclude\_query\_output=** ] exclude_query_output
    指定是否在電子郵件訊息中傳回查詢執行的輸出。exclude_query_output 是 bit,預設值是 0。當此參數是 0 時,sp_send_dbmail 預存程序的執行會在控制台上列印查詢執行結果所傳回的訊息。當此參數是 1 時,sp_send_dbmail 預存程序的執行不會在控制台上列印任何查詢執行訊息。
  • [ **@append\_query\_error=** ] append_query_error
    指定在 **@query** 引數指定的查詢傳回錯誤時,是否傳送電子郵件。append_query_errorbit,預設值是 0。當這個參數是 1 時,Database Mail 會傳送電子郵件訊息,且會在電子郵件訊息的主體中包含查詢錯誤訊息。當這個參數是 0 時,Database Mail 不會傳送電子郵件訊息,且 sp_send_dbmail 以傳回碼 1 為結尾,表示失敗。
  • [ **@query\_no\_truncate=** ] query_no_truncate
    指定執行查詢時是否使用選項來避免截斷大型變數長度資料類型 (varchar(max)nvarchar(max)varbinary(max)xmltextntextimage 和使用者自訂資料類型)。若有設定,查詢結果不包含資料行標頭。query_no_truncate 值的類型是 bit。當此值是 0 或未指定時,查詢中的資料行會截斷為 256 個字元。當此值是 1 時,不會截斷查詢中的資料行。這個參數的預設值是 0。

    ms190307.note(zh-tw,SQL.90).gif附註:
    搭配大量資料一起使用時,@query_no_truncate 選項會耗用其他資源並減慢伺服器效能。
  • [ **@query\_result\_no\_padding=** ] query_result_no_padding ]
    這個類型是位元。預設值是 0。將它設定為 1 時,就不會對查詢結果進行填補,而這可能減少檔案大小。

    如果您將 @query\_result\_no\_padding 設定為 1,而又設定 @query\_result\_width 參數,則 @query\_result\_no\_padding 參數會覆寫 @query\_result\_width 參數。在這種情況下,並不會發生錯誤。

    如果您將 @query\_result\_no\_padding 設定為 1,而又設定 @query\_no\_truncate 參數,則會引發錯誤。

  • [ **@mailitem\_id=** ] mailitem_id [ OUTPUT ]
    選擇性輸出參數會傳回訊息的 mailitem_idmailitem_id 的類型是 int

傳回碼值

0 (成功) 或 1 (失敗)

結果集

成功時,傳回「郵件已列入佇列」訊息。

備註

使用之前,您必須利用 Database Mail 組態精靈、SQL Server 介面區組態工具或 sp_configure 來啟用 Database Mail。

sysmail_stop_sp 會停止外部程式使用的 Service Broker 物件來停止 Database Mail。使用 sysmail_stop_sp 停止 Database Mail 時,sp_send_dbmail 仍接受郵件。若要啟動 Database Mail,請使用 sysmail_start_sp

當未指定 **@profile** 時,sp_send_dbmail 會使用預設的設定檔。如果傳送電子郵件訊息的使用者有預設私人設定檔,Database Mail 會使用這個設定檔。如果使用者沒有預設私人設定檔,sp_send_dbmail 會使用預設公用設定檔。如果使用者沒有預設私人設定檔,也沒有預設公用設定檔,sp_send_dbmail 就會傳回錯誤。

sp_send_dbmail 不支援沒有內容的電子郵件訊息。若要傳送電子郵件訊息,您至少必須指定 **@body**、**@query**、**@file\_attachments** 或 **@subject** 其中之一。否則,sp_send_dbmail 會傳回錯誤。

Database Mail 利用目前使用者的 Microsoft Windows 安全性內容來控制檔案的存取。因此,以 SQL Server 驗證方法驗證的使用者無法使用 **@file\_attachments** 附加檔案。Windows 不允許 SQL Server 在遠端電腦之間提供認證。因此,從執行 SQL Server 的電腦以外的電腦執行命令時,Database Mail 可能無法從網路共用附加檔案。

如果同時指定了 **@query** 和 **@file\_attachments**,但找不到檔案,仍會執行查詢,但不會傳送電子郵件。

當指定查詢時,結果集會格式化為內嵌文字。結果中的二進位資料會以十六進位格式傳送。

**@recipients**、**@copy\_recipients** 和 **@blind\_copy\_recipients** 等參數是用分號分隔的電子郵件地址清單。必須至少提供其中一個參數,否則,sp_send_dbmail 會傳回錯誤。

執行沒有交易內容的 sp_send_dbmail 時,Database Mail 會啟動並認可隱含交易。從現有交易執行 sp_send_dbmail 時,Database Mail 必須依賴使用者來認可或回復任何變更。它並不會啟動內部交易。

權限

msdb 資料庫中 DatabaseMailUser 資料庫角色的所有成員,都預設了 sp_send_dbmail 的執行權限。不過,當傳送訊息的使用者沒有使用要求設定檔的權限時,sp_send_dbmail 會傳回錯誤,且不會傳送訊息。

範例

A. 傳送電子郵件訊息

這個範例利用電子郵件地址 danw@Adventure-Works.com,將電子郵件訊息傳給 Dan Wilson。訊息的主旨是 Automated Success Message。訊息的主體包含 'The stored procedure finished successfully' 這個句子。

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'AdventureWorks Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message' ;

B. 利用查詢結果傳送電子郵件訊息

這個範例利用電子郵件地址 danw@Adventure-Works.com,將電子郵件訊息傳給 Dan Wilson。訊息的主旨是 Work Order Count,且會執行查詢來顯示在 2004 年 4 月 30 日之後 DueDate 小於兩天的工作訂單號碼。Database Mail 會將結果附加為一個文字檔。

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'AdventureWorks Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @query = 'SELECT COUNT(*) FROM AdventureWorks.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

C. 傳送 HTML 電子郵件訊息

這個範例利用電子郵件地址 danw@Adventure-Works.com,將電子郵件訊息傳給 Dan Wilson。訊息的主旨是 Work Order List,且包含一份 HTML 文件,其中顯示在 2004 年 4 月 30 日之後 DueDate 小於兩天的工作訂單。Database Mail 用 HTML 格式來傳送訊息。

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks.Production.WorkOrder as wo
              JOIN AdventureWorks.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2004-04-30'
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2 
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;

請參閱

參考

Database Mail 和 SQL Mail 預存程序 (Transact-SQL)
sp_addrolemember (Transact-SQL)

其他資源

Database Mail
Database Mail 設定檔
Database Mail 組態精靈
SQL Server 介面區組態

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本 歷程記錄

2005 年 12 月 5 日

新增內容:
  • 描述 sp_send_dbmail 的交易行為。