Database Mail Attachment Error

In the previous blog post, I wrote about how to configure and use the Sql Server Database Mail in our daily operations like alerting of errors or warnings or sending emails via Sql Server etc.

Today I will tell about an error when attaching a file to the email that we want to send.

Firstly, I want to express how to send an email with attachments. You can use below script to send the query result as attachment.

declare @query varchar(300) = 'SELECT *  
FROM [AdventureWorks2019].[Sales].[SalesOrderDetail]'

DECLARE @tab char(1) = CHAR(9)
EXEC msdb.dbo.sp_send_dbmail  
@profile_name='Notification' 
,@recipients ='someone@mail.com'
,@query= @query  
,@subject= 'Large File Attachment'  
,@attach_query_result_as_file=1    
,@query_attachment_filename='large_file_attachment.csv'    
,@query_result_separator=@tab   -- Define seperator
,@query_result_no_padding=1     -- Query result will not be padded
,@query_result_width=32767      -- line width between 10 and 32767

The query will return a result set and append this result set to email as attachment named the file as large_file_attachment.csv.

Up to now, we talked about how to send a result set as attachments. But sometimes this result set will be bigger than expected/allowed and got an error like this|

The problem is the size of query result which is exceeding 1000000 bytes (1MB) and this is the default value of MaxFileSize in the default mail settings.

The settings can be queried with the following stored procedure;

Exec msdb.dbo.sysmail_help_configure_sp

We can also see the setting values and change them using SQL Server Management Studio(SSMS).

Get Kendime Notlar’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Go to Object Explorer > Management and click Database Mail and choose View or change system parameters and click Next.

Here is the same values with the stored procedure (sysmail_help_configure_sp)

These values can be changed according to the needs. Here we will change the MaxFileSize to a bigger value in the System parameters part. Also following script can be used to change the parameter values.

exec msdb.dbo.sysmail_configure_sp 'MaxFileSize','5000000' -- (5MB)

After changing the setting value, email will be sent.

You can change other parameters with the same stored procedure like below;

exec msdb.dbo.sysmail_configure_sp 'ProhibitedExtensions','js,sh'

You can remove or add values to ProhibitedExtensions as an example and this stored procedure is used for all parameter changes.

Hope this post will be helpful.

Feel free to contribute to make my writings get better and usefu