Sp_send_dbmail parameter usage in Database Mail server

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]
[, [@mailitem_id =] mailitem_id] [OUTPUT]


Parameters

[ @profile_name = ] ' profile_name '

The name of the configuration file that sent the message. The type of profile_name is sysnameand the default value is NULL. profile_name must be the name of an existing Database Mail configuration file. If profile_nameis not specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile,sp_send_dbmail uses the default public profile for the msdb database. If the user does not have a default private profile and the database does not have a default public profile, you must specify a @profile_name.

[ @recipients = ] ' Recipients '

A semicolon-delimited list of e-mail addresses to send messages to. The recipient list is of type varchar (max). Although this parameter is an optional parameter, you must specify at least one of the @recipients,@copy_recipients , or @blind_copy_recipients , or sp_send_dbmail will return an error.

[ @copy_recipients = ] ' copy_recipients '

A semicolon-delimited list of e-mail addresses to CC messages to. The type of the CC recipient list is varchar (max). Although this parameter is an optional parameter, you must specify at least one of the @recipients,@copy_recipients , or @blind_copy_recipients , or sp_send_dbmail will return an error.

[ @blind_copy_recipients = ] ' blind_copy_recipients '

A semicolon-delimited list of e-mail addresses to be BCC to. The blind copy recipient list is of type varchar (max). Although this parameter is an optional parameter, you must specify at least one of the @recipients,@copy_recipients , or @blind_copy_recipients , or sp_send_dbmail will return an error.

[ @subject = ] ' subject '

The subject of the e-mail. The type of the theme is nvarchar (255). If no theme is specified, SQL Server message is the default.

[ @body = ] ' Body '

The body of the e-mail message. The message body type is nvarchar (max)and the default value is NULL.

[ @body_format = ] ' Body_format '

The format of the message body. The type of the parameter is varchar (a), and the default value is null. If specified, the header setting for outgoing messages indicates that the message body has the specified format. The parameter may contain one of the following values:

• TEXT
• Html

The default is TEXT.

[ @importance = ] ' Importance '

The importance of the message. The type of the parameter is varchar (6). The parameter may contain one of the following values:

• Low
• Normal
• High

The default value is Normal.

[ @sensitivity = ] ' Sensitivity '

The sensitivity of the message. The type of the parameter is varchar (a). The parameter may contain one of the following values:

• Normal
• Personal
• Private
• Confidential

The default value is Normal.

[ @file_attachments = ] ' file_attachments ',

The list of file names for e-mail attachments, separated by semicolons. The files in the list must be specified with an absolute path. The attachment list is of type nvarchar (max).

[ @query = ] ' Query '

The query to execute. Query results can be attached as files or included in the body of an e-mail message. The type of the query is nvarchar (max)and can contain any valid Transact-SQL statement. Note that the query executes in a separate session, so local variables in the script that calls sp_send_dbmail are not available for queries.

[ @execute_query_database = ] ' Execute_query_database '

The database context in which the stored procedure runs the query. The type of the parameter is sysname, which defaults to the current database. This parameter applies only when you specify a @query .

[ @attach_query_result_as_file = ] Attach_query_result_as_file

Specifies whether the query result set is returned as an attachment. The attach_query_result_as_file data type is bitand the default value is 0.

If the value is 0, the query results are included in the body of the e-mail message after the contents of the @body parameter. If the value is 1, the result is returned as an attachment. This parameter applies only when you specify a @query .

[ @query_attachment_filename =] Query_attachment_filename

Specifies the file name used by the query result set attachment. The query_attachment_filename data type is nvarchar (255)and the default value is NULL. If Attach_query_result is 0, this parameter is ignored. If Attach_query_result is 1 and this parameter is NULL, Database Mail creates any file name.

[ @query_result_header = ] Query_result_header

Specifies whether the query result contains column headings. The data type of the Query_result_header value is bit. If the value is 1, the query result contains the column headings. If the value is 0, the query results do not include column headings. The default value for this parameter is 1. This parameter applies only when you specify a @query .

[ @query_result_width =] Query_result_width

The line width (character) used to format the query results. The query_result_width data type is int, and the default value is 256. The value provided must be between 10 and 32767. This parameter applies only when you specify a @query .

[ @query_result_separator = ] ' Query_result_separator '

The character used to separate columns in the query output. The delimiter type is char (1). The default is "" (space).

[ @exclude_query_output = ] Exclude_query_output

Specifies whether to use e-mail to return output from query execution. The exclude_query_output data type is bit and the default value is 0. When this parameter is 0 o'clock, execution of thesp_send_dbmail stored procedure prints the message returned as the result of the query execution on the console. When this parameter is 1 o'clock, execution of thesp_send_dbmail stored procedure does not print any query execution messages on the console.

[ @append_query_error = ] append_query_error

The

Specifies whether to send an e-mail message when the query specified by the @query parameter returns an error. The data type of Append_query_error is bit , and the default value is 0. If the value of this parameter is 1, Database Mail sends an e-mail message, and the body of the e-mail message displays a query error. If the value of this parameter is 0, Database Mail does not send e-mail messages, and sp_send_dbmail returns code 1 at the end indicating failure.

[ @query_no_truncate = ] query_no_truncate

Specifies whether to use to avoid truncating large variable-length data types (varchar (max),nvarchar (max),varbinary (max),xml, the options for text,ntext,image , and user-defined types) execute the query. When this option is set, the query results will not contain column headings. The data type of the query_no_truncate value is bit. When the value is 0 or unspecified, the columns in the query are truncated to 256 characters. When the value is 1 o'clock, the columns in the query are not truncated. The default value for this parameter is 0.

Attention:
When used with large amounts of data, the @query_no_truncate option consumes additional resources and can reduce server performance.

[ @mailitem_id = ] mailitem_id [OUTPUT]

The optional output parameter returns the mailitem_idof the message. The type of mailitem_id is int.

Return code value

0 (Success) or 1 (failed)

Result set

On success, the message "message queued" is returned.

Note

You must enable Database Mail using the Database Mail Configuration Wizard, the SQL Server surface Area Configuration tool, or sp_configure before you use it.

sysmail_stop_sp stops Database Mail by stopping the Service Broker object that is used by the external program. After you use sysmail_stop_sp to stop Database Mail,sp_send_dbmail still accepts messages. To start Database Mail, use sysmail_start_sp.

If @profileis not specified, sp_send_dbmail uses the default profile. If the user who sends the e-mail message has a default private profile, Database Mail uses that profile. If the user does not have a default private profile, sp_send_dbmail will use the default public profile. If the user does not have a default private profile and a default public profile, sp_send_dbmail will return an error.

sp_send_dbmail does not support e-mail messages that do not have any content. To send an e-mail message, you must specify at least one of the @body,@query,@file_attachments , or @subject . Otherwise,sp_send_dbmail will return an error.

Database Mail Controls access to files using the current user's Microsoft Windows security context. Therefore, users who are authenticated by SQL Server cannot use @file_attachments attach files. Windows does not allow SQL Server to provide credentials to another remote computer from one remote computer. Therefore, if you run the command from a computer other than the computer that is running SQL Server, Database Mail may not be able to attach files from the network share.

If both @query and @file_attachments are specified and the file is not found, the query will still execute, but no e-mail will be sent.

After you specify a query, the format of the result set is set to inline text. Sends the binary data in the result using hexadecimal format.

The parameters @recipients,@copy_recipients , and @blind_copy_recipients are semicolon-delimited lists of e-mail addresses. At least one of the above parameters must be supplied, otherwise sp_send_dbmail will return an error.

When you execute sp_send_dbmail without a transaction context, Database Mail starts and submits an implicit transaction. When sp_send_dbmail is executed from within an existing transaction, Database Mail depends on the user committing or rolling back any changes. It does not start an internal transaction.

Permissions

By default, all members of the Databasemailuser database role in themsdb database have Execute permissions on sp_send_dbmail . However, if the user who sends the message does not have permission to use the configuration file for the request,sp_send_dbmail returns an error and does not send the message.

Example A. Send e-mail

This example sends an e-mail message to Dan Wilson using an email address [email protected] . The subject of the message is automated Success message. The message body contains a word ' The stored procedure finished successfully '.

EXEC [Email protected]_name = ' AdventureWorks Administrator ', @recipients = ' [email protected] ', @body = ' The stored procedu Re finished successfully ', @subject = ' automated Success Message ';

B. Sending an e-mail with query results

This example sends an e-mail message to Dan Wilson using an email address [email protected] . The subject of the message is Work order Count, and the query is executed to show the DueDate in the two days after April 30, 2004. Database Mail attaches the result as a text file.

EXEC [Email protected]_name = ' AdventureWorks Administrator ', @recipients = ' [email protected] ', @query = ' SELECT COUNT (*) F ROM AdventureWorks.Production.WorkOrderWHERE duedate > ' 2004-04-30 '  and DATEDIFF (DD, ' 2004-04-30 ', duedate ) < 2 ', @subject = ' work Order Count ', @attach_query_result_as_file = 1;

A. Sending an HTML e-mail

This example sends an e-mail message to Dan Wilson using an email address [email protected] . The subject of the message is the work order listand contains an HTML document that lists DueDate 's ticket within 2nd of April 30, 2004. Database Mail uses HTML format to send the message.

 DECLARE @tableHTML NVARCHAR (MAX); SET @tableHTML =n ' 
Above from http://www.cnblogs.com/adandelion/articles/1045760.html
Here is a personal example:
--after the metabase message is finished--where l_test is the configuration file name, test is the message subject name, the message body content body, the query results in the message queries, My_test is the attachment name--query results in the textEXECMsdb.dbo.sp_send_dbmail@profile_name = 'l_test',    @recipients = '[email protected]',    @subject = 'Test',    @body = 'The query is:',    @query = 'SELECT TOP [id],[user_id],[date_time] from [xxx]. [dbo]. [xxx] WHERE [date_time] > "2011-06-20 09:52:40.730" ORDER by ID ASC',    @attach_query_result_as_file= 0    --the results of the query are placed in the attachment    EXECMsdb.dbo.sp_send_dbmail@profile_name = 'l_test',    @recipients = '[email protected]; [Email protected]',    @subject = 'Test',    @body = 'The query is in Fujian',    @query = 'SELECT TOP [Id],[user_id],[date_time] from [xxxdb.[ DBO]. [xxxxx] WHERE [date_time] > "2011-06-20 09:52:40.730" ORDER by ID ASC',    @attach_query_result_as_file= 1,    @query_attachment_filename= 'my_test'
Sp_send_dbmail parameter usage in Database Mail server