Send Emails with Attachments in SQL Server (T-SQL)

 

SQL Server provides us with the ability to  send email via its Database Mail solution. This includes a bunch of stored procedures that facilitate the configuration and sending of emails.

To send an email, use the sp_send_dbmail stored procedure. This procedure accepts a bunch of optional arguments, one of which allows you to send attachments.

Actually, there are two arguments that enable you to send attachments. The one you use will depend on the context.

They are:

• @file_attachments – Allows you to specify a file to attach to the email.
• @attach_query_result_as_file – This is only applicable if you’re also using @query to email the results of a query.

Examples below.

Attach a File

The @file_attachments argument allows you to specify a file to attach to the email.

Here’s an example:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DB Admin Profile',  
    @recipients = 'admin@example.com',  
    @body = 'The attached file contains all required data.',
    @file_attachments = 'C:/Files/MailAttachment.pdf',
    @subject = 'File attached as discussed';

The value must include the absolute path to the file.

By default, the maximum file size is 1 MB per file, but you can change this limit as: 

The Error

Here’s the error you’ll receive if you try to send an attachment that’s larger than the allowable attachment file size:

Msg 22051, Level 16, State 1, Line 0
File attachment or query results size exceeds allowable value of 1000000 bytes.

Check the Limit

If you haven’t yet received an error, you might not know what the limit is. You can use the sysmail_help_configure_sp stored procedure in the msdb database to check the attachment file size limit.

EXEC msdb.dbo.sysmail_help_configure_sp 'MaxFileSize';

Result:

+-------------+--------------+---------------------------+
| paramname   | paramvalue   | description               |
|-------------+--------------+---------------------------|
| MaxFileSize | 1000000      | Default maximum file size |
+-------------+--------------+---------------------------+

In this example I passed MaxFileSize as an argument to narrow the results to just the configuration option that I’m interested in. You can also use sysmail_help_configure_sp procedure without any arguments to return all configuration options.

Change the Limit

You can change the attachment file size limit with the sysmail_configure_sp stored procedure.

EXEC msdb.dbo.sysmail_configure_sp 'MaxFileSize','3000000';

Now when we execute sysmail_help_configure_sp again, we see the new value.

EXEC msdb.dbo.sysmail_help_configure_sp 'MaxFileSize';

Result:

+-------------+--------------+---------------------------+
| paramname   | paramvalue   | description               |
|-------------+--------------+---------------------------|
| MaxFileSize | 3000000      | Default maximum file size |
+-------------+--------------+---------------------------+

Note that the description has remained the same. In this case it’s a bit misleading because 1000000 is the default maximum file size – not 4000000.

You can change the description with the same procedure. In fact, you can specify the parameter name=value pairs as well, if you prefer (this is also true for the sysmail_configure_sp procedure).

Let’s do it again, but this time I’ll update the description, and I’ll use the name=value pairs.

EXECUTE msdb.dbo.sysmail_configure_sp  
    @parameter_name = 'MaxFileSize', 
    @parameter_value = '4000000',
    @description = 'Current maximum file size';

Now when we execute sysmail_help_configure_sp again, we see the new value.

EXEC msdb.dbo.sysmail_help_configure_sp 
    @parameter_name = 'MaxFileSize';

Result:

+-------------+--------------+---------------------------+
| paramname   | paramvalue   | description               |
|-------------+--------------+---------------------------|
| MaxFileSize | 4000000      | Current maximum file size |
+-------------+--------------+---------------------------+

Attach Multiple Files

You can attach multiple files by separating each file name with a semi-colon:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DB Admin Profile',  
    @recipients = 'admin@example.com',  
    @body = 'The attached files contain all required data.',
    @file_attachments = 'C:/FIle1.pdf;C:/FIle2.pdf',
    @subject = 'Files attached as discussed';

As mentioned, the file size limit applies to each file. So if the file size limit is 1 MB, each file in this example can be up to 1 MB (resulting in a total size of 2 MB for both attachments combined).

Attach Results of a Query

You can also use Database Mail to email the result of a query. This is done with the @query argument. By default, the results are listed in the body of the email, but you can add them as an attachment if you prefer.

To add them as an attachment, set the @attach_query_result_as_file argument with a value of 1.

Here’s an example of sending query results as an attachment:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DB Admin Profile',  
    @recipients = 'admin@example.com',  
    @body = 'Potential candidates for an Admin job, perhaps?',
    @query = 'SELECT TOP(5) * FROM Artists;',
    @execute_query_database = 'Music',
    @attach_query_result_as_file = 1,
    @subject = 'Query results as discussed';

There are quite a few options available to you when sending query results in an email.

For example, you can use @query_attachment_filename to specify the file name (if not specified, Database Mail will make one up). You can also use the @query_result_header argument to specify whether or not the headers are included in the query, and you can use the @query_result_no_padding argument to remove any padding that’s automatically applied to the results.

Thank you.