sp_send_dbmail (Transact-SQL)sp_send_dbmail (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

將電子郵件訊息傳送給指定的收件者。Sends an e-mail message to the specified recipients. 訊息可能包含查詢結果集、檔案附件,或兩者皆有。The message may include a query result set, file attachments, or both. 當 mail 成功放在 Database Mail 佇列中時, sp_send_dbmail會傳回訊息的mailitem_idWhen mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message. 這個預存程式是在msdb資料庫中。This stored procedure is in the msdb database.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

  
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]  
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]  
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]  
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]  
    [ , [ @from_address = ] 'from_address' ]  
    [ , [ @reply_to = ] 'reply_to' ]   
    [ , [ @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 ]  

引數Arguments

[ @profile_name = ] 'profile_name'這是要傳送訊息之來源設定檔的名稱。[ @profile_name = ] 'profile_name' Is the name of the profile to send the message from. Profile_name的類型是sysname,預設值是 Null。The profile_name is of type sysname, with a default of NULL. Profile_name必須是現有 Database Mail 設定檔的名稱。The profile_name must be the name of an existing Database Mail profile. 未指定profile_name時, sp_send_dbmail會使用目前使用者的預設私人設定檔。When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. 如果使用者沒有預設的私人設定檔, sp_send_dbmail會使用msdb資料庫的預設公用設定檔。If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. 如果使用者沒有預設的私人設定檔,而且沒有資料庫的預設公用設定檔, @ 則必須指定 profile_name。If the user does not have a default private profile and there is no default public profile for the database, @profile_name must be specified.

[ @recipients = ] 'recipients'這是要傳送訊息的電子郵件地址清單(以分號分隔)。[ @recipients = ] 'recipients' Is a semicolon-delimited list of e-mail addresses to send the message to. 收件者清單的類型為Varchar (max)The recipients list is of type varchar(max). 雖然此參數是 @選擇性的, 但至少必須指定其中一個收件者、 @copy_recipients@blind_copy_recipients ,否則sp_send_dbmail會傳回錯誤。Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @copy_recipients = ] 'copy_recipients'這是用來複製訊息的電子郵件地址清單(以分號分隔)。[ @copy_recipients = ] 'copy_recipients' Is a semicolon-delimited list of e-mail addresses to carbon copy the message to. 複製收件者清單的類型為Varchar (max)The copy recipients list is of type varchar(max). 雖然此參數是 @選擇性的, 但至少必須指定其中一個收件者、 @copy_recipients@blind_copy_recipients ,否則sp_send_dbmail會傳回錯誤。Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @blind_copy_recipients = ] 'blind_copy_recipients'這是以分號分隔的電子郵件地址清單,用來將訊息複製到其中。[ @blind_copy_recipients = ] 'blind_copy_recipients' Is a semicolon-delimited list of e-mail addresses to blind carbon copy the message to. [密件副本收件者] 清單的類型為Varchar (max)The blind copy recipients list is of type varchar(max). 雖然此參數是 @選擇性的, 但至少必須指定其中一個收件者、 @copy_recipients@blind_copy_recipients ,否則sp_send_dbmail會傳回錯誤。Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @from_address = ] 'from_address'這是電子郵件訊息的 [寄件者位址] 的值。[ @from_address = ] 'from_address' Is the value of the 'from address' of the email message. 這是選擇性參數,用來覆寫郵件設定檔中的設定。This is an optional parameter used to override the settings in the mail profile. 這個參數的類型為Varchar (MAX)This parameter is of type varchar(MAX). SMTP 安全性設定會決定是否要接受這些覆寫。SMTP security settings determine if these overrides are accepted. 如果沒有指定參數,預設值為 NULL。If no parameter is specified, the default is NULL.

[ @reply_to = ] 'reply_to'這是電子郵件訊息之 [回復位址] 的值。[ @reply_to = ] 'reply_to' Is the value of the 'reply to address' of the email message. 它只接受一個電子郵件地址做為有效的值。It accepts only one email address as a valid value. 這是選擇性參數,用來覆寫郵件設定檔中的設定。This is an optional parameter used to override the settings in the mail profile. 這個參數的類型為Varchar (MAX)This parameter is of type varchar(MAX). SMTP 安全性設定會決定是否要接受這些覆寫。SMTP security settings determine if these overrides are accepted. 如果沒有指定參數,預設值為 NULL。If no parameter is specified, the default is NULL.

[ @subject = ] 'subject'這是電子郵件訊息的主旨。[ @subject = ] 'subject' Is the subject of the e-mail message. 主體的類型為Nvarchar (255)The subject is of type nvarchar(255). 如果未指定主旨,預設值便是「SQL Server 訊息」。If no subject is specified, the default is 'SQL Server Message'.

[ @body = ] 'body'這是電子郵件訊息的主體。[ @body = ] 'body' Is the body of the e-mail message. 訊息主體的類型為Nvarchar (max) ,預設值為 Null。The message body is of type nvarchar(max), with a default of NULL.

[ @body_format = ] 'body_format'這是訊息主體的格式。[ @body_format = ] 'body_format' Is the format of the message body. 參數的類型為Varchar (20) ,預設值為 Null。The parameter is of type varchar(20), with a default of NULL. 當指定這個選項時,會設定外寄訊息的標頭來表示訊息主體有指定的格式。When specified, the headers of the outgoing message are set to indicate that the message body has the specified format. 參數可包含下列各值之一:The parameter may contain one of the following values:

  • TEXTTEXT

  • HTMLHTML

預設值是 TEXT。Defaults to TEXT.

[ @importance = ] 'importance'這是訊息的重要性。[ @importance = ] 'importance' Is the importance of the message. 參數的類型為Varchar (6)The parameter is of type varchar(6). 參數可包含下列各值之一:The parameter may contain one of the following values:

  • Low

  • 一般Normal

  • High

預設值是 Normal。Defaults to Normal.

[ @sensitivity = ] 'sensitivity'這是訊息的敏感度。[ @sensitivity = ] 'sensitivity' Is the sensitivity of the message. 參數的類型為Varchar (12)The parameter is of type varchar(12). 參數可包含下列各值之一:The parameter may contain one of the following values:

  • 一般Normal

  • PersonalPersonal

  • PrivatePrivate

  • ConfidentialConfidential

預設值是 Normal。Defaults to Normal.

[ @file_attachments = ] 'file_attachments'這是要附加至電子郵件訊息的檔案名清單(以分號分隔)。[ @file_attachments = ] 'file_attachments' Is a semicolon-delimited list of file names to attach to the e-mail message. 清單中的檔案必須指定為絕對路徑。Files in the list must be specified as absolute paths. 附件清單的類型為Nvarchar (max)The attachments list is of type nvarchar(max). 根據預設,Database Mail 會將檔案附件限制為每個檔案 1 MB。By default, Database Mail limits file attachments to 1 MB per file.

重要

因為此參數無法存取本機檔案系統,所以無法在 Azure SQL 受控執行個體中使用。This parameter is not available in Azure SQL Managed Instance because it cannot access local file system.

[ @query = ] 'query'這是要執行的查詢。[ @query = ] 'query' Is a query to execute. 查詢的結果可以附加成一個檔案,也可以包含在電子郵件訊息的主體中。The results of the query can be attached as a file, or included in the body of the e-mail message. 查詢的類型為Nvarchar (max) ,而且可以包含任何有效Transact-SQLTransact-SQL的語句。The query is of type nvarchar(max), and can contain any valid Transact-SQLTransact-SQL statements. 請注意,查詢會在個別的會話中執行,因此查詢不會提供呼叫sp_send_dbmail之腳本中的區域變數。Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

[ @execute_query_database = ] 'execute_query_database'這是預存程式在其中執行查詢的資料庫內容。[ @execute_query_database = ] 'execute_query_database' Is the database context within which the stored procedure runs the query. 參數的類型是sysname,預設值是目前的資料庫。The parameter is of type sysname, with a default of the current database. 只有在 @指定 query時,這個參數才適用。This parameter is only applicable if @query is specified.

[ @attach_query_result_as_file = ] attach_query_result_as_file指定是否以附加檔案的方式傳回查詢的結果集。[ @attach_query_result_as_file = ] attach_query_result_as_file Specifies whether the result set of the query is returned as an attached file. attach_query_result_as_file的類型為bit,預設值是0。attach_query_result_as_file is of type bit, with a default of 0.

當值為0時,查詢結果會包含在電子郵件訊息的本文中, @主體參數的內容之後。When the value is 0, the query results are included in the body of the e-mail message, after the contents of the @body parameter. 當值是 1 時,會以附加檔案的方式傳回結果。When the value is 1, the results are returned as an attachment. 只有在 @指定 query時,這個參數才適用。This parameter is only applicable if @query is specified.

[ @query_attachment_filename = ] query_attachment_filename指定要用於查詢附件之結果集的檔案名。[ @query_attachment_filename = ] query_attachment_filename Specifies the file name to use for the result set of the query attachment. query_attachment_filename的類型為Nvarchar (255) ,預設值為 Null。query_attachment_filename is of type nvarchar(255), with a default of NULL. attach_query_result為0時,會忽略這個參數。This parameter is ignored when attach_query_result is 0. attach_query_result是1,且這個參數是 Null 時,Database Mail 會建立任意檔案名。When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.

[ @query_result_header = ] query_result_header指定查詢結果是否包含資料行標頭。[ @query_result_header = ] query_result_header Specifies whether the query results include column headers. Query_result_header 值的類型為bitThe query_result_header value is of type bit. 當值是 1 時,查詢結果會包含資料行標頭。When the value is 1, query results contain column headers. 當值是 0 時,查詢結果不會包含資料行標頭。When the value is 0, query results do not include column headers. 此參數的預設值為1This parameter defaults to 1. 只有在 @指定 query時,這個參數才適用。This parameter is only applicable if @query is specified.

注意

將設定@query_result_header為0,並將設定@query_no_truncate為1時,可能會發生下列錯誤:The following error may occur when setting @query_result_header to 0 and setting @query_no_truncate to 1:
訊息22050,層級16,狀態1,行12:無法初始化 sqlcmd 程式庫,錯誤號碼為-2147024809。Msg 22050, Level 16, State 1, Line 12: Failed to initialize sqlcmd library with error number -2147024809.

[ @query_result_width = ] query_result_width這是用來格式化查詢結果的線條寬度(以字元為單位)。[ @query_result_width = ] query_result_width Is the line width, in characters, to use for formatting the results of the query. Query_result_width的類型為int,預設值是256。The query_result_width is of type int, with a default of 256. 提供的值必須介於 10 和 32767 之間。The value provided must be between 10 and 32767. 只有在 @指定 query時,這個參數才適用。This parameter is only applicable if @query is specified.

[ @query_result_separator = ] 'query_result_separator'這是在查詢輸出中用來分隔資料行的字元。[ @query_result_separator = ] 'query_result_separator' Is the character used to separate columns in the query output. 分隔符號的類型為char (1)The separator is of type char(1). 預設值是 ' ' (空白)。Defaults to ' ' (space).

[ @exclude_query_output = ] exclude_query_output指定是否要在電子郵件訊息中傳回查詢執行的輸出。[ @exclude_query_output = ] exclude_query_output Specifies whether to return the output of the query execution in the e-mail message. exclude_query_output是 bit,預設值是0。exclude_query_output is bit, with a default of 0. 當這個參數為0時, sp_send_dbmail預存程式的執行會列印在主控台上執行查詢的結果所傳回的訊息。When this parameter is 0, the execution of the sp_send_dbmail stored procedure prints the message returned as the result of the query execution on the console. 當這個參數是1時, sp_send_dbmail預存程式的執行並不會在主控台上列印任何查詢執行訊息。When this parameter is 1, the execution of the sp_send_dbmail stored procedure does not print any of the query execution messages on the console.

[ @append_query_error = ] append_query_error指定在 @查詢引數中指定的查詢傳回錯誤時,是否傳送電子郵件。[ @append_query_error = ] append_query_error Specifies whether to send the e-mail when an error returns from the query specified in the @query argument. append_query_errorbit,預設值是0。append_query_error is bit, with a default of 0. 當這個參數是 1 時,Database Mail 會傳送電子郵件,且會在電子郵件的主體中包含查詢錯誤訊息。When this parameter is 1, Database Mail sends the e-mail message and includes the query error message in the body of the e-mail message. 當此參數為0時,Database Mail 不會傳送電子郵件訊息,而且sp_send_dbmail會以傳回碼1結束,表示失敗。When this parameter is 0, Database Mail does not send the e-mail message, and sp_send_dbmail ends with return code 1, indicating failure.

[ @query_no_truncate = ] query_no_truncate指定是否要使用選項來執行查詢,以避免截斷大型可變長度資料類型(Varchar (max)Nvarchar (max)Varbinary (max)xmltextNtextimage、和使用者定義的資料類型)。[ @query_no_truncate = ] query_no_truncate Specifies whether to execute the query with the option that avoids truncation of large variable length data types (varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, and user-defined data types). 若有設定,查詢結果不包含資料行標頭。When set, query results do not include column headers. Query_no_truncate值的類型為bitThe query_no_truncate value is of type bit. 當此值是 0 或未指定時,查詢中的資料行會截斷為 256 個字元。When the value is 0 or not specified, columns in the query truncate to 256 characters. 當此值是 1 時,不會截斷查詢中的資料行。When the value is 1, columns in the query are not truncated. 這個參數的預設值是 0。This parameter defaults to 0.

注意

當與大量資料搭配使用時,@query_no_truncate選項會耗用額外的資源,而且可能會降低伺服器效能。When used with large amounts of data, the @query_no_truncate option consumes additional resources and can slow server performance.

[ @query_result_no_padding ] @query_result_no_padding此類型為 bit。[ @query_result_no_padding ] @query_result_no_padding The type is bit. 預設值為 0。The default is 0. 當您將設為1時,不會填補查詢結果,可能會減少檔案大小。如果您將@query_result_no_padding設定為1,並@query_result_width設定參數@query_result_width ,則@query_result_no_padding參數會覆寫參數。When you set to 1, the query results are not padded, possibly reducing the file size.If you set @query_result_no_padding to 1 and you set the @query_result_width parameter, the @query_result_no_padding parameter overwrites the @query_result_width parameter.

在此情況下,不會發生任何錯誤。In this case no error occurs.

注意

將設定@query_result_no_padding為1並提供的參數時,可能會發生下列錯誤:@query_no_truncate:The following error may occur when setting @query_result_no_padding to 1 and providing a parameter for @query_no_truncate:
訊息22050,層級16,狀態1,行0:無法執行查詢@query_result_no_append ,因為和@query_no_truncate選項互斥。Msg 22050, Level 16, State 1, Line 0: Failed to execute the query because the @query_result_no_append and @query_no_truncate options are mutually exclusive.

如果您將設定@query_result_no_padding為1,並@query_no_truncate設定參數,就會引發錯誤。If you set the @query_result_no_padding to 1 and you set the @query_no_truncate parameter, an error is raised.

[ @mailitem_id = ] mailitem_id [ OUTPUT ]選擇性輸出參數會傳回訊息的mailitem_id[ @mailitem_id = ] mailitem_id [ OUTPUT ] Optional output parameter returns the mailitem_id of the message. Mailitem_id的類型為intThe mailitem_id is of type int.

傳回碼值Return Code Values

傳回碼為 0 表示成功。A return code of 0 means success. 其他任何值都表示失敗。Any other value means failure. 失敗之語句的錯誤碼會儲存在 @@ERROR變數中。The error code for the statement that failed is stored in the @@ERROR variable.

結果集Result Sets

成功時,傳回「郵件已列入佇列」訊息。On success, returns the message "Mail queued."

備註Remarks

使用之前,必須先使用 Database Mail Configuration Wizard 或sp_configure來啟用 Database Mail。Before use, Database Mail must be enabled using the Database Mail Configuration Wizard, or sp_configure.

sysmail_stop_sp會停止外部程式所使用的 Service Broker 物件來停止 Database Mail。sysmail_stop_sp stops Database Mail by stopping the Service Broker objects that the external program uses. 當使用sysmail_stop_sp停止 Database Mail 時, sp_send_dbmail仍會接受郵件。sp_send_dbmail still accepts mail when Database Mail is stopped using sysmail_stop_sp. 若要開始 Database Mail,請使用sysmail_start_spTo start Database Mail, use sysmail_start_sp.

若未指定 profile,sp_send_dbmail會使用預設配置@ 檔。When @profile is not specified, sp_send_dbmail uses a default profile. 如果傳送電子郵件訊息的使用者有預設私人設定檔,Database Mail 會使用這個設定檔。If the user sending the e-mail message has a default private profile, Database Mail uses that profile. 如果使用者沒有預設的私人設定檔, sp_send_dbmail會使用預設的公用設定檔。If the user has no default private profile, sp_send_dbmail uses the default public profile. 如果沒有使用者的預設私人設定檔,而且沒有預設的公用設定檔,則sp_send_dbmail會傳回錯誤。If there is no default private profile for the user and no default public profile, sp_send_dbmail returns an error.

sp_send_dbmail不支援沒有內容的電子郵件訊息。sp_send_dbmail does not support e-mail messages with no content. 若要傳送電子郵件訊息,您必須至少指定其中一個 @主體@查詢@file_attachments @或主旨To send an e-mail message, you must specify at least one of @body, @query, @file_attachments, or @subject. 否則, sp_send_dbmail會傳回錯誤。Otherwise, sp_send_dbmail returns an error.

Database Mail 利用目前使用者的 MicrosoftMicrosoft Windows 安全性內容來控制檔案的存取。Database Mail uses the MicrosoftMicrosoft Windows security context of the current user to control access to files. 因此,使用SQL ServerSQL Server驗證進行驗證的使用者無法使用 @file_attachments附加檔案。Therefore, users who are authenticated with SQL ServerSQL Server Authentication cannot attach files using @file_attachments. Windows 不允許 SQL ServerSQL Server 在遠端電腦之間提供認證。Windows does not allow SQL ServerSQL Server to provide credentials from a remote computer to another remote computer. 因此,從執行 SQL ServerSQL Server 的電腦以外的電腦執行命令時,Database Mail 可能無法從網路共用附加檔案。Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL ServerSQL Server runs on.

如果同時 @ 指定了查詢和 @file_attachments ,而且找不到檔案,則查詢仍會執行,但不會傳送電子郵件。If both @query and @file_attachments are specified and the file cannot be found, the query is still executed but the e-mail is not sent.

當指定查詢時,結果集會格式化為內嵌文字。When a query is specified, the result set is formatted as inline text. 結果中的二進位資料會以十六進位格式傳送。Binary data in the result is sent in hexadecimal format.

[收件者 @ ]、[ @ copy_recipients] 和 [blind_copy_recipients @] 參數是以分號分隔的電子郵件地址清單。The parameters @recipients, @copy_recipients, and @blind_copy_recipients are semicolon-delimited lists of e-mail addresses. 至少必須提供這些參數的其中一個,否則sp_send_dbmail會傳回錯誤。At least one of these parameters must be provided, or sp_send_dbmail returns an error.

執行沒有交易內容的sp_send_dbmail時,Database Mail 會啟動並認可隱含交易。When executing sp_send_dbmail without a transaction context, Database Mail starts and commits an implicit transaction. 從現有的交易內執行sp_send_dbmail時,Database Mail 會依賴使用者來認可或回復任何變更。When executing sp_send_dbmail from within an existing transaction, Database Mail relies on the user to either commit or roll back any changes. 它並不會啟動內部交易。It does not start an inner transaction.

PermissionsPermissions

Sp_send_dbmail的執行許可權預設為Msdb資料庫中DatabaseMailUser資料庫角色的所有成員。Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. 不過,當傳送訊息的使用者沒有使用該要求設定檔的許可權時, sp_send_dbmail會傳回錯誤,且不會傳送訊息。However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

範例Examples

A.A. 傳送電子郵件訊息Sending an e-mail message

這個範例會使用電子郵件地址myfriend@Adventure-Works.com,將電子郵件訊息傳送給您的朋友。This example sends an e-mail message to your friend using the e-mail address myfriend@Adventure-Works.com. 訊息的主旨是 Automated Success MessageThe message has the subject Automated Success Message. 訊息的主體包含 'The stored procedure finished successfully' 這個句子。The body of the message contains the sentence 'The stored procedure finished successfully'.

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

B.B. 利用查詢結果傳送電子郵件訊息Sending an e-mail message with the results of a query

這個範例會使用電子郵件地址yourfriend@Adventure-Works.com,將電子郵件訊息傳送給您的朋友。This example sends an e-mail message to your friend using the e-mail address yourfriend@Adventure-Works.com. 訊息的主旨是 Work Order Count,且會執行查詢來顯示在 2004 年 4 月 30 日之後 DueDate 小於兩天的工作訂單數目。The message has the subject Work Order Count, and executes a query that shows the number of work orders with a DueDate less than two days after April 30, 2004. Database Mail 會將結果附加為一個文字檔。Database Mail attaches the result as a text file.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Adventure Works Administrator',  
    @recipients = 'yourfriend@Adventure-Works.com',  
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.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.C. 傳送 HTML 電子郵件訊息Sending an HTML e-mail message

這個範例會使用電子郵件地址yourfriend@Adventure-Works.com,將電子郵件訊息傳送給您的朋友。This example sends an e-mail message to your friend using the e-mail address yourfriend@Adventure-Works.com. 訊息的主旨是 Work Order List,且包含一份 HTML 文件,其中顯示在 2004 年 4 月 30 日之後 DueDate 小於兩天的工作訂單。The message has the subject Work Order List, and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2004. Database Mail 會使用 HTML 格式來傳送訊息。Database Mail sends the message in HTML format.

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='yourfriend@Adventure-Works.com',  
    @subject = 'Work Order List',  
    @body = @tableHTML,  
    @body_format = 'HTML' ;  

另請參閱See Also

Database Mail Database Mail
Database Mail 設定物件 Database Mail Configuration Objects
Database Mail 預存(程式 transact-sql) Database Mail Stored Procedures (Transact-SQL)
sp_addrolemember (Transact-SQL)sp_addrolemember (Transact-SQL)