In SQL Server, you can send emails using the sp_send_dbmail stored procedure in the msdb database.

One of the choices you have when executing this procedure is whether or not to include the results of a query.

Another choice you have is whether or not to send those results in an attachment.

Example

To email the results of a query, use the @query argument.

To attach those results as an attachment, use the @attach_query_result_as_file argument. This is accepts a bit value, with the default being 0 (for no attachment).

To send the results in an attachment, simply provide a 1 for this argument.

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';

This example sends an email with the results attached as a .txt file. The name was automatically generated by Database Mail.

By default, the maximum file size for attachments is 1 MB per file, but you can change this with the sysmail_configure_sp stored procedure.

Provide a Name for the Attachment

You also have the option of providing your own name for the attachment. You can do this with the @query_attachment_filename argument.

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,
    @query_attachment_filename = 'Artists.csv',
    @subject = 'Query results as discussed';

More Options

Here’s another example that expands on the previous one. Here, I’ve included a number of options that you might find useful when sending query results as an email 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,
    @query_attachment_filename = 'Artists.csv',
    @query_result_header = 1,
    @query_result_width = 256,
    @query_result_separator = ',',
    @exclude_query_output = 1,
    @append_query_error = 1,
    @query_no_truncate = 0,
    @query_result_no_padding = 1,
    @subject = 'Query results as discussed';

Some of these are set to their default value and some aren’t. For example, I’ve changed @query_result_no_padding to 1 in order to remove padding from within the columns. I’ve also used @query_result_separator to change the separator from the default space, to a comma.

The sp_send_dbmail procedure accepts more arguments than I’ve listed here. See Microsoft’s documentation for a full list of arguments and their descriptions.

Configuring Database Mail

The examples on this page assume that you’ve already enabled and configured Database Mail.

If you don’t have Database Mail configured, see How to Send Email from SQL Server. That article provides examples for enabling and configuring Database Mail with T-SQL.