SQL Server Agent is one of my favorite tools – it allows you to automatically schedule jobs, alert you if things are going badly, and capture information into database tables.
One common request is to execute a query and send the results via email on a regular basis. This task is a great match for SQL Server Agent and sp_send_dbmail. Setting up a job and a step that uses sp_send_dbmail with the @query parameter, and building on it with other available parameters, you can easily accomplish this.
Let’s take a look at a job I’ve created. The first step is to gather metrics from the sys.dm_os_sys_memory DMV. I insert the data into a table in my master database.
The second step in this job uses sp_send_dbmail to send the results of the query from today to a database mail profile account.
Let’s look at the command more closely.
@profile_name = 'Main DB Mail profile',
@recipients = 'firstname.lastname@example.org',
@subject = 'Memory Values',
@query = N'SELECT total_physical_memory_kb, available_physical_memory_kb, system_memory_state_desc, collection_date_time
WHERE CAST(collection_date_time AS Date) = CAST(GETDATE() AS DATE)
ORDER BY collection_date_time DESC;',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Memory Values.txt'
- @profile_name is the Database Mail profile you want to use to send the email. You must have at least one profile already set up.
- @recipients is the list of email addresses to send the email to. If you have more than one, separate them with a semi-colon.
- @subject is the email subject.
- @query is the query you wish to run. MSDN says it “can contain any valid Transact-SQL statements”, but I haven’t tested the limits of this statement yet.
- @attach_query_results_as_file has two options: 0 or 1. When set to 0, the query results are included in the body of the email. When set to 1, the results are included as an attachment.
- @query_attachment_filename is optional, and is the name and file extension of the attachment. I recommend setting this, with a .txt or .csv extension.
There are other options you can add as well, such as @execute_query_database, @query_result_header, @query_result_width, and more.
When I execute the job, I receive this email:
This isn’t well-formatted, so I may want to modify the results with @query_result_header and @query_result_width.
Both SQL Server Agent jobs and sp_send_dbmail give you a lot of flexibility for creating and emailing query results. Learn to use them effectively!