SQL configuration email 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 ' ]
[ , [@ Sensiti.pdf = ]   ' Sensiti.pdf ' ]
[ , [@ 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 email.Profile_nameIsSysnameThe default value is null.Profile_nameIt must be the name of the existing database email configuration file. If not specifiedProfile_name, ThenSp_send_dbmailUse the default dedicated configuration file of the current user. If the user does not have a default dedicated configuration file,Sp_send_dbmailYesMSDBThe default public configuration file of the database. If you do not have a default dedicated configuration file and the database does not have a default public configuration file, you must specify@ Profile_name.

[ @ Recipients =] ' Recipients '

The list of email addresses to which you want to send emails, separated by semicolons. The recipient list type isVarchar (max). Although this parameter is optional, you must specify at least@ Recipients,@ Copy_recipientsOr@ Blind_copy_recipientsOrSp_send_dbmailWill return an error.

[ @ Copy_recipients =] ' Copy_recipients '

The list of email addresses to copy to, separated by semicolons. The type of the CC Recipient List isVarchar (max). Although this parameter is optional, you must specify at least@ Recipients,@ Copy_recipientsOr@ Blind_copy_recipientsOrSp_send_dbmailWill return an error.

[ @ Blind_copy_recipients =] ' Blind_copy_recipients '

The list of email addresses that you want to copy to a BCC, separated by semicolons. The type of the BCC copy Recipient List isVarchar (max). Although this parameter is optional, you must specify at least@ Recipients,@ Copy_recipientsOr@ Blind_copy_recipientsOrSp_send_dbmailWill return an error.

[ @ Subject =] ' Subject '

The subject of the email. The topic type isNvarchar (255). If no topic is specified, the default value is "SQL Server Message ".

[ @ Body =] ' Body '

Body of the email. The mail body type isNvarchar (max)The default value is null.

[ @ Body_format =] ' Body_format '

The format of the email body. The parameter type isVarchar (20), The default value is null. If this parameter is specified, the header setting of the mail to be sent indicates that the mail body has the specified format. This parameter may contain one of the following values:

• Text
• Html

The default value is text.

[ @ Importance =] ' Importance '

The importance of the email. The parameter type isVarchar (6). This parameter may contain one of the following values:

• Low
• Normal
• High

The default value is normal.

[ @ Sensiti.pdf =] ' Sensiti.pdf '

Email sensitivity. The parameter type isVarchar (12). This parameter may contain one of the following values:

• Normal
• Personal
• Private
• Confidential

The default value is normal.

[ @ File_attachments =] ' File_attachments ',

List of file names of email attachments, separated by semicolons. You must use the absolute path to specify the files in the list. The Attachment List type isNvarchar (max).

[ @ Query =] ' Query '

The query to be executed. The query result can be appended to a file or included in the body of the email. The query type isNvarchar (max)And can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, soSp_send_dbmailLocal variables in the script cannot be used for query.

[ @ Execute_query_database =] ' Execute_query_database '

The context of the database where the stored procedure runs the query. The parameter type isSysnameThe default value is the current database. Only when@ Query.

[ @ Attach_query_result_as_file =] Attach_query_result_as_file

Specify whether the query result set is returned as an attachment.Attach_query_result_as_fileThe data type of isBitThe default value is 0.

If the value is 0, the query result is included in the body of the email.@ BodyAfter the parameter content. If the value is 1, the result is returned as an attachment. Only when@ Query.

[ @ Query_attachment_filename =] Query_attachment_filename

Specify the file name used by the query result set attachment.Query_attachment_filenameThe data type of isNvarchar (255)The default value is null. IfAttach_query_resultIf the value is 0, this parameter is ignored. IfAttach_query_resultIf this parameter is set to 1 and null, any file name is created in the database mail.

[ @ Query_result_header =] Query_result_header

Specifies whether the query result contains the column title. The data type of query_result_header value isBit. If the value is 1, the query result contains the column title. If the value is 0, the query result does not contain the column title. The default value of this parameter is1. Only when@ Query.

[ @ Query_result_width=] Query_result_width

Used to set the line width (character) of the query result format ).Query_result_widthThe data type of isIntThe default value is 256. The value must be between 10 and 32767. Only when@ Query.

[ @ Query_result_separator =] ' Query_result_separator '

Used to separate the characters of columns in the query output. The Separator Type isChar (1). The default value is "" (Space ).

[ @ Exclude_query_output =] Exclude_query_output

Whether to use an email to return the query output.Exclude_query_outputThe data type of is bit, and the default value is 0. When this parameter is 0,Sp_send_dbmailThe execution of stored procedures will print messages returned as query results on the console. When this parameter is set to 1,Sp_send_dbmailThe execution of stored procedures does not print any query execution messages on the console.

[ @ Append_query_error =] Append_query_error

Specify whether@ QueryAn email is sent when an error is returned for the query specified by the parameter.Append_query_errorThe data type of isBitThe default value is 0. If the value of this parameter is 1, the database mail will send an email with the query error message displayed in the body of the email. If the value of this parameter is 0, the database mail will not send emails,Sp_send_dbmailWill return at the endCode1, indicating failure.

[ @ Query_no_truncate =] Query_no_truncate

Specifies whether to use to avoid truncation of large variable-length data types (Varchar (max),Nvarchar (max),Varbinary (max),XML,Text,Ntext,ImageAnd user-defined type. After this option is set, the query result does not contain the column title.Query_no_truncateThe data type of the value isBit. When the value is 0 or not specified, the column in the query will be truncated to 256 characters. When the value is 1, the column in the query is not truncated. The default value of this parameter is 0.

Note:
When used with a large amount of data ,@Query_no_truncateThis option will occupy other resources and reduce the server performance.

 

[ @ Mailitem_id =] Mailitem_id[Output]

Optional.Mailitem_id.Mailitem_idIsInt.

Return code value

0 (successful) or 1 (failed)

Result set

If the message is successful, the message "the email is queued" is returned ".

Remarks

Before use, you must use the database mail Configuration Wizard, the SQL Server peripheral application configurator tool orSp_configureEnable database mail.

Sysmail_stop_spBy stopping externalProgramThe Service Broker object used to stop database mail. UseSysmail_stop_spAfter the database email is stopped,Sp_send_dbmailEmails are still accepted. To start database mail, useSysmail_start_sp.

If not specified@ Profile, ThenSp_send_dbmailUse the default configuration file. If the user who sends an email has a default dedicated configuration file, the database mail uses this configuration file. If the user does not have a default dedicated configuration fileSp_send_dbmailThe default public configuration file will be used. If you do not have a default private configuration file or a default public configuration fileSp_send_dbmailWill return an error.

Sp_send_dbmailEmails without any content are not supported. To send an email, you must specify at least@ Body,@ Query,@ File_attachmentsOr@ Subject. Otherwise,Sp_send_dbmailAn error is returned.

Database mail uses the current user's Microsoft Windows security context to control access to files. Therefore, users authenticated by SQL Server cannot use@ File_attachmentsAppend a file. Windows does not allow SQL server to provide creden。 from one remote computer to another. Therefore, if you run this command from a computer other than the computer that runs SQL Server, database emails may not be able to share additional files from the network.

If both@ QueryAnd@ File_attachmentsIf no file is found, the query is still executed, but no email is sent.

After a query is specified, the result set format is set to inline text. The binary data in the result is sent in hexadecimal format.

Parameters@ Recipients,@ Copy_recipientsAnd@ Blind_copy_recipientsList of email addresses separated by semicolons. At least one of the preceding parameters must be provided. OtherwiseSp_send_dbmailAn error is returned.

Execute without the transaction ContextSp_send_dbmailDatabase mail starts and commits implicit transactions. Execute from existing transactionsSp_send_dbmailDatabase emails will depend on the user to submit or roll back any changes. It does not start internal transactions.

Permission

By default,MSDBIn the databaseDatabasemailuserAll member pairs of the database roleSp_send_dbmailAll have the execution permission. However, if the user who sent the email does not have the permission to use the configuration file of the request,Sp_send_dbmailAn error is returned and the email is not sent.

Example

A. send an email

This example uses an email addressDanw@Adventure-Works.comSend an email to Dan Wilson. The subject of this email isAutomatic success message. The email body contains one sentence.'The Stored Procedure finished successfully'.

Copy code

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. send an email containing the query results

This example uses an email addressDanw@Adventure-Works.comSend an email to Dan Wilson. The subject of this email isWork order countAnd run the query to displayDuedateThe number of work orders within two days after January 1, April 30, 2004. Database mail attaches the result as a text file.

Copy code

 
Exec MSDB. DBO. sp_send_dbmail @ profile_name = 'adventureworks administrator ', @ recipients = 'danw @ Adventure-Works.com', @ query = 'select count (*) from adventureworks. production. workorderwhere duedate> ''2017-04-30 ''and datediff (DD, ''2017-04-30'', duedate) <2', @ subject = 'work order count ', @ attach_query_result_as_file = 1;
 

A. Send an HTML email

This example uses an email addressDanw@Adventure-Works.comSend an email to Dan Wilson. The subject of the email isWork order listAnd contains an HTML document, which listsDuedateA ticket within two days after January 1, April 30, 2004. Database emails are sent in HTML format.

Copy code

Declare @ tablehtml nvarchar (max ); set @ tablehtml = n'