Sql Server Sp_send_dbmail File Attachment

пятница 15 февраляadmin
Sql Server Sp_send_dbmail File Attachment Rating: 5,6/10 3889 reviews
-->

APPLIES TO: SQL Server Azure SQL Database (Managed Instance only) Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Sends an e-mail message to the specified recipients. The message may include a query result set, file attachments, or both. When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message. This stored procedure is in the msdb database.

Syntax

Arguments

[ @profile_name = ] 'profile_name'Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is 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 there is no default public profile for the database, @profile_name must be specified.

[ @recipients = ] 'recipients'Is a semicolon-delimited list of e-mail addresses to send the message to. The recipients list is of type varchar(max). 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.

They all should be and not /. Also your files appear to be the same, so make sure you are sending unique files. See if that helps and let me know. I return to work on Monday (been on maternity leave), so I can help you then if you are still having problems. Tara Kizer Microsoft MVP for Windows Server System - SQL Server.

[ @copy_recipients = ] 'copy_recipients'Is a semicolon-delimited list of e-mail addresses to carbon copy the message to. The copy recipients list is of type varchar(max). 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'Is a semicolon-delimited list of e-mail addresses to blind carbon copy the message to. The blind copy recipients list is of type varchar(max). 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'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. This parameter is of type varchar(MAX). SMTP security settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.

[ @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. This parameter is of type varchar(MAX). SMTP security settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.

[ @subject = ] 'subject'Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is 'SQL Server Message'.

[ @body = ] 'body'Is the body of the e-mail message. The message body is of type nvarchar(max), with a default of NULL.

[ @body_format = ] 'body_format'Is the format of the message body. 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:

  • TEXT

  • HTML

Defaults to TEXT.

[ @importance = ] 'importance'Is the importance of the message. The parameter is of type varchar(6). The parameter may contain one of the following values:

  • Low

  • Normal

  • High

Defaults to Normal.

[ @sensitivity = ] 'sensitivity'Is the sensitivity of the message. The parameter is of type varchar(12). The parameter may contain one of the following values:

  • Normal

  • Personal

  • Private

  • Confidential

Defaults to Normal.

[ @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. The attachments list is of type nvarchar(max). By default, Database Mail limits file attachments to 1 MB per file.

Important

Download miroslav philharmonik sound dvd 2. SampleTank 3 is the latest version of the IK Multimedia sound and groove workstation virtual instrument with an extensive sound, instrument, and groove library IK Multimedia - Miroslav Philharmonik 2. Masterfully recorded by a Master. Made in collaboration with legendary jazz bassist and sampling visionary, Miroslav Vitous, Miroslav Philharmonik 2 gives you a over 58GB sound library with over 2,700 instruments sampled in glorious high-definition audio. Every instrument fits — they are made to play together and made to stand alone.

This parameter is not available in Azure SQL Managed Instance because it cannot access local file system.

[ @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. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. 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'Is the database context within which the stored procedure runs the query. The parameter is of type sysname, with a default of the current database. This parameter is only applicable if @query is specified.

[ @attach_query_result_as_file = ] attach_query_result_as_fileSpecifies whether the result set of the query is returned as an attached file. attach_query_result_as_file is of type bit, with a default of 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. When the value is 1, the results are returned as an attachment. This parameter is only applicable if @query is specified.

[ @query_attachment_filename = ] query_attachment_filenameSpecifies the file name to use for the result set of the query attachment. query_attachment_filename is of type nvarchar(255), with a default of NULL. This parameter is ignored when attach_query_result is 0. When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.

[ @query_result_header = ] query_result_headerSpecifies whether the query results include column headers. The query_result_header value is of type bit. When the value is 1, query results contain column headers. When the value is 0, query results do not include column headers. This parameter defaults to 1. This parameter is only applicable if @query is specified.

Note

The following error may occur when setting @query_result_header to 0 and setting @query_no_truncate to 1:
Msg 22050, Level 16, State 1, Line 12: Failed to initialize sqlcmd library with error number -2147024809.

[ @query_result_width = ] query_result_widthIs the line width, in characters, to use for formatting the results of the query. The query_result_width is of type int, with a default of 256. The value provided must be between 10 and 32767. This parameter is only applicable if @query is specified.

[ @query_result_separator = ] 'query_result_separator'Is the character used to separate columns in the query output. The separator is of type char(1). Defaults to ' ' (space).

[ @exclude_query_output = ] exclude_query_outputSpecifies whether to return the output of the query execution in the e-mail message. exclude_query_output is bit, with a default of 0. 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. 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_errorSpecifies whether to send the e-mail when an error returns from the query specified in the @query argument. append_query_error is bit, with a default of 0. 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. 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_truncateSpecifies 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. The query_no_truncate value is of type bit. When the value is 0 or not specified, columns in the query truncate to 256 characters. When the value is 1, columns in the query are not truncated. This parameter defaults to 0.

Note

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_paddingThe type is bit. The default is 0. 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.

Note

The following error may occur when setting @query_result_no_padding to 1 and providing a parameter for @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.

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 ]Optional output parameter returns the mailitem_id of the message. The mailitem_id is of type int.

Return Code Values

A return code of 0 means success. Any other value means failure. The error code for the statement that failed is stored in the @@ERROR variable.

Result Sets

On success, returns the message 'Mail queued.'

Remarks

Before use, Database Mail must be enabled using the Database Mail Configuration Wizard, or sp_configure.

sysmail_stop_sp stops Database Mail by stopping the Service Broker objects that the external program uses. sp_send_dbmail still accepts mail when Database Mail is stopped using sysmail_stop_sp. To start Database Mail, use sysmail_start_sp.

When @profile is not specified, sp_send_dbmail uses a default profile. If the user sending the e-mail message has a default private profile, Database Mail uses that profile. If the user has no default private profile, sp_send_dbmail uses the default public profile. If there is no default private profile for the user and no default public profile, sp_send_dbmail returns an error.

sp_send_dbmail does not support e-mail messages with no content. To send an e-mail message, you must specify at least one of @body, @query, @file_attachments, or @subject. Otherwise, sp_send_dbmail returns an error.

Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. 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 Server runs on.

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.

The parameters @recipients, @copy_recipients, and @blind_copy_recipients are semicolon-delimited lists of e-mail addresses. At least one of these parameters must be provided, or sp_send_dbmail returns an error.

When executing sp_send_dbmail without a transaction context, Database Mail starts and commits an implicit transaction. 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.

Permissions

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. 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. Sending an e-mail message

This example sends an e-mail message to your friend using the e-mail address [email protected]. The message has the subject Automated Success Message. The body of the message contains the sentence 'The stored procedure finished successfully'.

B. Sending an e-mail message with the results of a query

This example sends an e-mail message to your friend using the e-mail address [email protected]. 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 attaches the result as a text file.

C. Sending an HTML e-mail message

This example sends an e-mail message to your friend using the e-mail address [email protected]. 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 sends the message in HTML format.

See Also

Database Mail
Database Mail Configuration Objects
Database Mail Stored Procedures (Transact-SQL)
sp_addrolemember (Transact-SQL)

By: Ranga Babu Updated: 2017-01-03 Comments (2) Related: More >Database Mail

Problem

We setup SQL Server Database Mail and use it to send alerts via email. We recently received the following error 'File attachment or query results size exceeds allowable value of 1000000 bytes.' when sending an attachment in the email. How can this be fixed?

Solution

Here is an example of trying to send a large Excel file and getting the error message 'file attachment or query results size exceeds allowable value'.

When this code runs, it returns the following error:

Check SQL Server Database Mail Settings

We can check the SQL Server Database Mail parameter properties by running the following stored procedure: sysmail_help_configure_sp.

Sql send mail

This stored procedure returns the SQL Server Database Mail parameters and parameter values.

Change SQL Server Database Mail Settings with Management Studio

We can also check the settings using SQL Server Management Studio (SSMS). Go to Management > Database Mail and right click and select Configure Database Mail. Select Next until you get to the Select Configuration Task screen and select View or change system parameters and select Next.

Here we can see the current setting for Maximum File Size (Bytes). The default is 1,000,000 bytes which is 1MB. The maximum value we can use 2,147,483,647, which is roughly 2GB. If we try to use a value greater than 2147483647 it will throw an error. So change the value to the necessary size for your needs.

Another option that we can change is the Prohibited Attachment File Extensions. This will allow us to block specific file extensions as attachments.

Change SQL Server Database Mail Settings with T-SQL

We can also change these parameter values using sysmail_configure_sp as follows.

Next Steps
  • Read these additional tips about Database Mail.

Last Updated: 2017-01-03



About the author
Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.
View all my tips