Sending Query Results by Email

A daily report is useful only when it reaches someone who can read it. Sending query results by email with Database Mail works well for small operational checks when the format and limits are deliberate.

Make Database Mail Ready

sp_send_dbmail uses a configured Database Mail profile and Service Broker-backed mail queue. Before sending query results by email, confirm the profile, permissions, recipient, and outbound route. Test a harmless message to the intended mailbox. A procedure returning a mail item ID means the item was accepted for processing, not that the recipient received it.

I check the Database Mail logs when a report goes missing. Network mail policies, profile configuration, and recipient filtering can all affect delivery. The query result is only one part of the path. Record who owns the mail route and how failures are alerted. An invisible queue is a poor place for a critical warning to wait.

Sending Small Query Results by Email Inline

The @query parameter runs a T-SQL statement and adds its result to the message. Keep the query read-only and narrow. The @execute_query_database parameter selects the database context for the query. Specify a profile when your instance uses more than one. Use a test recipient first when sending query results by email and verify how columns render in the actual mail client.

The example sends a small database-state check. Replace the address and profile with approved values. A production alert should name the instance and capture time in the subject or body. A subject that says SQL report tells the reader almost nothing.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'OperationsProfile',
    @recipients = N'dba@example.com',
    @subject = N'SQL Server database states',
    @body = N'Current database states follow.',
    @execute_query_database = N'master',
    @query = N'SELECT @@SERVERNAME AS instance_name, name, state_desc FROM sys.databases ORDER BY name;';

Choose an Attachment When Sending Wider Query Results by Email

@attach_query_result_as_file places the query output in an attachment. Use a clear file name and a separator that the receiving tool can parse. The output remains a text rendering of a query result, not a guaranteed spreadsheet. Check for quoted values, embedded separators, and long text before asking anyone to import it. For complex exports, use a purpose-built data pipeline.

I use attachments when the output is wider than an email window or needs to be saved. Keep it small enough for Database Mail and the organization’s mail size limits. If the report can grow without bound, add filters, a row limit, or a different delivery method. The inbox should not become an accidental data warehouse.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'OperationsProfile',
    @recipients = N'dba@example.com',
    @subject = N'SQL Server database inventory',
    @execute_query_database = N'master',
    @query = N'SELECT name, state_desc, recovery_model_desc FROM sys.databases ORDER BY name;',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = N'database-inventory.txt',
    @query_result_separator = N',';

Format an HTML Table With Care

Database Mail accepts HTML body format, but @query output is not automatically a polished HTML table. For a small fixed report, build HTML carefully from known columns and encode data values that can contain markup. Do not concatenate untrusted database content into raw HTML without escaping it. A malicious or simply odd value can break the layout.

I prefer plain text for operational alerts because it survives mail clients and forwarding. Use HTML when a table genuinely improves reading, then test it in the team’s mail tools. Keep color from being the only signal. Include labels and values so the message still works in a plain text preview. The mail should answer what changed and what the recipient should check.

Understand Query Output Controls

sp_send_dbmail has parameters for result width, header inclusion, and error handling. A narrow width can truncate display or wrap columns unexpectedly. Test with representative long names and null values. Large results hit both Database Mail and downstream mail limits. A report that silently loses rows is worse than a clear error.

I include a row count or threshold in the message when it helps the reader verify completeness. I also keep the query itself in operations documentation. The sent report should be reproducible from the source SQL, not a mystery attachment that appears each morning. If you change the query, record why and test the output format again.

Monitor the Mail Queue

msdb contains Database Mail status and event information. Query the sent, unsent, and failed items when delivery is questioned. Inspect the mail event log for profile or SMTP errors. A successful SQL Agent job that calls sp_send_dbmail can still leave a mail item failed later. Job status and delivery status are separate checks.

The following query gives a recent mail-item view. Treat recipients and message content as sensitive operational data. Limit who can read the history.

SELECT TOP (50)
       mailitem_id,
       sent_status,
       send_request_date,
       sent_date,
       subject
FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC;

Avoid Excessive Sensitive Data

Email is easy to forward and retain outside the database. Do not send customer records or secrets because a query makes it convenient. Use aggregated counts or exception summaries for alerts. If row-level details are needed, direct the recipient to an approved secure system. Apply the organization’s retention and classification rules to attachments.

I ask what decision the recipient needs to make. Then I send the minimum data for that decision. A failed-job email needs the server, job, time, and error, not a full dump of application tables. Smaller messages are easier to deliver and easier to act on.

Make Failure Visible When Sending Query Results by Email

A daily report can stop arriving without anyone noticing. Schedule a separate check of Database Mail errors and define an owner for the report. If the email itself is the only alert channel, a mail failure can hide every other problem. Use an independent monitoring path for critical failures.

I put the expected send time and recipient in the runbook. If someone asks why the report is missing, we can check the job, queue, and mail system in order. That sequence is faster than rerunning the query and hoping. Delivery deserves its own evidence.

Keep the Report Actionable

Use a clear subject, a capture time, and a concise explanation of exceptions. Avoid mailing the same full inventory every hour if only changes matter. A recipient who learns that most messages require no action will stop reading them. Send a summary with links to approved internal detail where appropriate, but keep the body free of unnecessary data.

What should happen after this email arrives? If the answer is unclear, improve the report before scheduling it. Database Mail is a transport. The operational value comes from the query, context, and follow-up process you design around it.

Related reading on this blog: Send Email From SQL Server: Configure Database Mail: SQL in Sixty Seconds #039: Video and Stop Growing MSDB Database by Removing sysmail_mailitems History.

A sent query result is not an alert, it is a message that needs a reader and an action.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.