SQL Server provides an easy way to email the results of a query to yourself (or to someone else).

To send email with T-SQL, you need to use the sp_send_dbmail stored procedure in the msdb database. This procedure accepts many arguments, one of which is the @query argument. That’s the argument that attaches the results of your query to the email.

Example

Here’s a basic example that uses a minimum of arguments:

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

By default, the results of the query are listed in the body of the email. You can alternatively have the results sent as an attachment.

Remove Padding

If your results are formatted with too much padding within the columns, you can use @query_result_no_padding = 1 to eliminate this padding.

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',
    @query_result_no_padding = 1,
    @subject = 'Query results as discussed';

Add Query Results as an Attachment

You can use the @attach_query_result_as_file argument to add the query results as an attachment. 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,
    @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';

In this example I’ve added some more arguments.

The @query_attachment_filename argument allows you to specify your own file name (if you don’t provide this argument, Database Mail will make one up).

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 T-SQL examples for enabling and configuring Database Mail. It doesn’t take long.