Set the SQL Server:sp_send_dbmail parameter

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @recipients = ] 'recipients [ ; SQL <wbr>Server: sp_send_dbmailParameter settings(转载)n ]' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @copy_recipients = ] 'copy_recipient [ ; SQL <wbr>Server: sp_send_dbmailParameter settings(转载)n ]' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; SQL <wbr>Server: sp_send_dbmailParameter settings(转载)n ]' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @subject = ] 'subject' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @body = ] 'body' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @body_format = ] 'body_format' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @importance = ] 'importance' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @sensitivity = ] 'sensitivity' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @file_attachments = ] 'attachment [ ; SQL <wbr>Server: sp_send_dbmailParameter settings(转载)n ]' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @query = ] 'query' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @execute_query_database = ] 'execute_query_database' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @query_attachment_filename = ] query_attachment_filename ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @query_result_header = ] query_result_header ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @query_result_width = ] query_result_width ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @query_result_separator = ] 'query_result_separator' ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @exclude_query_output = ] exclude_query_output ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @append_query_error = ] append_query_error ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @query_no_truncate = ] query_no_truncate ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
SQL <wbr>Server: sp_send_dbmailParameter settings(转载)

Parameters
[ @profile_name= ] 'profile_name' Name of the send mail configuration file. Profile_name is of type sysname, The default value is NULL. Profile_name must be the name of an existing Database Mail configuration file. If profile_name is not specified, then sp_send_dbmail Default private profile using the current user. If the user does not have a default private profile, sp_send_dbmail Use msdb Public default profile database. If the user does not have a default private profile, and the database is not the common configuration file by default, you must specify the @profile_name.
[ @recipients= ] 'recipients' To send the message to a list of e-mail addresses, separated by semicolons. Type a recipient list for varchar(max). Although this parameter is optional, but must specify at least @recipients, @copy_recipients Or @blind_copy_recipients One, otherwise sp_send_dbmail Returns an error.
[ @copy_recipients= ] 'copy_recipients' To send mail list of e-mail addresses, separated by semicolons. Copy recipients list is of type varchar(max). Although this parameter is optional, but must specify at least @recipients, @copy_recipients Or @blind_copy_recipients One, otherwise sp_send_dbmail Returns an error.
[ @blind_copy_recipients= ] 'blind_copy_recipients' To blind carbon copy the message to the list of e-mail addresses, separated by semicolons. Type blind carbon copy recipients list is varchar(max). Although this parameter is optional, but must specify at least @recipients, @copy_recipients Or @blind_copy_recipients One, otherwise sp_send_dbmail Returns an error.
[ @subject= ] 'subject' The subject of the email. The theme of the type nvarchar(255). If the subject is not specified, the default is “ the SQL Server message”.
[ @body= ] 'body' The body of the e-mail. Type the text of the message to nvarchar(max), The default value is NULL.
[ @body_format= ] 'body_format' The message body format. This parameter of type varchar(20), The default value is NULL. If specified, the headers of the outgoing message are set to indicate that the message body with the specified format. This parameter may contain one of the following values:

TEXT
HTML

The default is TEXT.
[ @importance= ] 'importance' The importance of the mail. This parameter of type varchar(6). This parameter may contain one of the following values:

Low
Normal
High

The default value is Normal.
[ @sensitivity= ] 'sensitivity' Mail sensitivity. This parameter of type varchar(12). This parameter may contain one of the following values:

Normal
Personal
Private
Confidential

The default value is Normal.
[ @file_attachments= ] 'file_attachments', A list of the names of files as email attachments, separated by semicolons. Must use an absolute path specified in the list of files. Annex list is of type nvarchar(max).
[ @query= ] 'query' The query to be executed. The results of the query can be attached as a file, or contained in the body of the message. The type of query for nvarchar(max), And can contain any valid Transact-SQL statement. Please note, query execution in a separate session, so call sp_send_dbmail Local variables in the script cannot be used for query.
[ @execute_query_database= ] 'execute_query_database' The stored procedure in which to run the query the database context. This parameter of type sysname, The default is the current database. Only in the specified @query This parameter is only applicable when,.
[ @attach_query_result_as_file= ] Attach_query_result_as_file specifies a query result set is returned as an attachment. The attach_query_result_as_file data type bit, The default value is 0.
If the value is 0, the query result is included in the body of the message, in @body After the argument. If the value is 1, the results were returned as an attachment. Only in the specified @query This parameter is only applicable when,.
[ @query_attachment_filename= ] Query_attachment_filename specifies a query result set attachment file name to use. The query_attachment_filename data type nvarchar(255), 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 will create any file name.
[ @query_result_header= ] Query_result_header specifies whether the query results contain column headings. The value of query_result_header data type bit. If the value is 1, the query results contain column headings. If the value is 0, the query results do not contain column headings. The default value for this parameter is 1. Only in the specified @query This parameter is only applicable when,.
[ @query_result_width = ] Query_result_width is used for line width format the query results of (character). The query_result_width data type int, The default value is 256. The value must be between 10 and 32767. Only in the specified @query This parameter is only applicable when,.
[ @query_result_separator= ] 'query_result_separator' Used to separate columns in the query output character. The separator is of type char(1). The default is “ ” (spaces).
[ @exclude_query_output= ] Exclude_query_output specifies whether the returned query execution output using e-mail. exclude_query_output A bit data type, the default value is 0. When this parameter is 0, sp_send_dbmail Stored procedure execution on the console will print as query execution result and return messages. When this parameter is 1, sp_send_dbmail Stored procedure does not print any query execution messages on the console.
[ @append_query_error= ] Append_query_error specifies whether the @query Send e-mail parameters specify the query returns the error. append_query_error Data type bit, The default value is 0. If this parameter is 1, then the database mail sends the email, text and e-mail in the query and display the error message. If this parameter is 0, the database mail does not send e-mail, sp_send_dbmail In the end return code 1, indicating failure.
[ @query_no_truncate= ] Query_no_truncate specifies whether to use can avoid the truncation of large variable length data types(varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image And user defined types) option executing query. This option is set, the query results will not contain column headings. The value of query_no_truncate data type bit. When the value is 0 or is not specified, the columns in the query will be truncated to 256 characters. When the value is 1, columns in the query are not truncated. The default value of this parameter is 0.
Note: for use with large amounts of data, @query_no_truncate Option will take additional resources, and can reduce the performance of the server.


[ @mailitem_id= ] mailitem_id [ OUTPUT ] Optional output parameters will return messages in mailitem_id. Mailitem_id is of type int.


SQL <wbr>Server: sp_send_dbmailParameter settings(转载) The return code values
0 (success) or 1 (failure)



SQL <wbr>Server: sp_send_dbmailParameter settings(转载) The result set
When successful, returns the message &ldquo message has been queuing;”.



SQL <wbr>Server: sp_send_dbmailParameter settings(转载) Remarks
Before use, must use the Database Mail configuration wizard, SQL Server surface area configuration tool or sp_configure Enable Database Mail.
sysmail_stop_sp By stopping the external program using the Service Broker object to stop the database mail. Use sysmail_stop_sp Database Mail, sp_send_dbmail Will accept mail. If you want to start the database mail, please use the sysmail_start_sp.
If not specified @profile, Then sp_send_dbmail Using the default configuration file. If the sending e-mail users have a default private profile, database mail uses the configuration file. If the user does not have a default private profile, then sp_send_dbmail To use the default public profile. If the user does not have a default private profile and the default public profile, then sp_send_dbmail Returns an error.
sp_send_dbmail Does not support without any email. To send e-mail messages, you must specify at least @body, @query, @file_attachments Or @subject One of. Otherwise, sp_send_dbmail Returns an error.
Database Mail uses the current user's Microsoft Windows security context to control access to files. Therefore, through the SQL Server authenticated users can not use @file_attachments Additional files. Windows does not allow SQL Server to provide credentials from a remote computer to a remote computer. So, if you run the command from the outside to run the SQL Server computer, database mail may not share additional files from the Internet.
If you have specified at the same time @query And @file_attachments And the file not found, the query will continue, but will not send e-mail.
When a query is specified, the result set is formatted as an inline text. Binary data using sixteen hexadecimal format to send the results of.
Parameters @recipients, @copy_recipients And @blind_copy_recipients Is a semicolon separated list of e-mail addresses. At least one of the above parameters must be provided, otherwise sp_send_dbmail Returns an error.
In the absence of transaction execution context sp_send_dbmail Database Mail will start, and submit the implicit transaction. From the implementation of the existing transaction. sp_send_dbmail , database mail will rely on users to commit or rollback any changes. It doesn't start internal affairs.



SQL <wbr>Server: sp_send_dbmailParameter settings(转载) Jurisdiction
By default, msdb In the database DatabaseMailUser All members of a database role to sp_send_dbmail Have permission to execute. However, if the send mail users do not have to use the requested configuration file permissions, sp_send_dbmail Returns an error and does not send the message.



SQL <wbr>Server: sp_send_dbmailParameter settings(转载) Example

A. email

This example uses the email address to the Dan Wilson e-mail. The subject of the message Automated Success Message. The message body contains a word 'The stored procedure finished successfully'.
EXEC msdb.dbo.sp_send_dbmail@profile_name = 'AdventureWorks Administrator',@recipients = '',@body = 'The stored procedure finished successfully.',@subject = 'Automated Success Message' ;


B. sends the query results emails

This example uses the email address to the Dan Wilson e-mail. The mail theme for Work Order Count, and execute the query to display the DueDate in 2004 April 30 days two days work. Database Mail will the results add to a text file.
EXEC msdb.dbo.sp_send_dbmail@profile_name = 'AdventureWorks Administrator',@recipients = '',@query = 'SELECT COUNT(*) FROM 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. send HTML e-mail

This example uses the email address to the Dan Wilson e-mail. Mail theme for Work Order List, and contains a HTML document, which lists the DueDate in 2004 April 30 days two days work sheet. Database Mail uses the HTML format to send the message.
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.OrderQtyFROM AdventureWorks.Production.WorkOrder as woJOIN AdventureWorks.Production.Product AS pON wo.ProductID = p.ProductIDWHERE DueDate > '2004-04-30'AND DATEDIFF(dd, '2004-04-30', DueDate) <2ORDER BY DueDate ASC,(p.ListPrice - p.StandardCost) * wo.OrderQty DESCFOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +N'</table>' ;EXEC msdb.dbo.sp_send_dbmail @recipients='',@subject = 'Work Order List',@body = @tableHTML,@body_format = 'HTML' ;

Posted by Beatrice at November 22, 2013 - 1:14 AM