SQL Server provides us with the ability to send emails 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 = '[email protected]',  
    @body = 'The attached file contains all required data.',
    @file_attachments = '/var/opt/mssql/bak/Nature.bak',
    @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 with the sysmail_configure_sp stored procedure.

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 = '[email protected]',  
    @body = 'The attached files contain all required data.',
    @file_attachments = '/var/opt/mssql/bak/Nature.bak;/var/opt/mssql/bak/World.bak',
    @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 = '[email protected]',  
    @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.