Home / Guides / Email a SQL Server query as Excel every Monday
Email a SQL Server query as Excel every Monday
Someone wants last week's orders, sign-ups or failed jobs in their inbox every Monday morning, as a spreadsheet. This guide shows what SQL Server can do on its own, where that stops, and how to send the result as a real Excel file at 07:00 every Monday with NotifyMeWhen, including on SQL Server Express.
Short answer.
SQL Server Agent plus Database Mail can email a query result as a text or CSV attachment, but not as an Excel file, and neither
is available in SQL Server Express. NotifyMeWhen's scheduled SQL report runs the query itself on a schedule such as
0 0 7 ? * MON (Mondays at 07:00), with dates such as "start of last week", and emails the result as an
.xlsx file through your own mail server, optionally only when there are rows.
The built-in way: SQL Server Agent and Database Mail
On Standard or Enterprise edition, an Agent job with a Monday schedule can call msdb.dbo.sp_send_dbmail, which runs a
query and attaches the result as a file:
USE [msdb];
EXEC dbo.sp_send_dbmail
@profile_name = N'Reports',
@recipients = N'sales@contoso.com',
@subject = N'Orders last week',
@query = N'SET NOCOUNT ON;
SELECT OrderID, CustomerName, OrderDate, Total
FROM Sales.dbo.Orders
WHERE OrderDate >= DATEADD(day, -7, CAST(GETDATE() AS date))
AND OrderDate < CAST(GETDATE() AS date);',
@attach_query_result_as_file = 1,
@query_attachment_filename = N'orders.csv',
@query_result_separator = N',';
It works, with limits worth knowing before you promise someone a spreadsheet:
- Not Excel. The attachment is plain text with a separator you choose. Excel opens a CSV, but number and date formats, leading zeros and long text are guessed again on every open.
- Not on Express. SQL Server Express has neither SQL Server Agent nor Database Mail, so there is nothing to run the schedule or send the mail.
- Dates by hand. "Last week" has to be worked out in T-SQL inside the query, and the job runs whatever the query says, read-only or not.
- Always sends. An empty result still produces an email unless you add your own
IF EXISTSlogic.
With NotifyMeWhen: a scheduled SQL report
NotifyMeWhen is a Windows service with a desktop app. Its watch type SQL Server: scheduled report runs one read-only query on a schedule and delivers the result as an Excel, CSV or PDF file. The service keeps the schedule itself, so it needs no SQL Server Agent and works against SQL Server Express.
1. Tell it how to send mail
In the app, open File > Delivery Settings and fill in your SMTP server (host, port, user and password). The password is encrypted on the machine with Windows DPAPI. No Microsoft Entra app is needed for email. Use Test to send yourself a message.
2. Create the report
Open Watches, add a watch of type SQL Server: scheduled report and fill in:
| Field | Value for "last week's orders, every Monday" |
|---|---|
| Server and Database | Your SQL Server and the database the query reads |
| Query | One SELECT (or WITH) using @FromDate and @ToDate, below |
| Parameters | @FromDate = start of last week and @ToDate = start of this week, one per line |
| Week starts on | Monday |
| Schedule | 0 0 7 ? * MON, Mondays at 07:00 in the machine's local time (the default; Build... picks it for you) |
| File format | Excel |
| Send | Always, or Only when the query returns rows |
| Email the file | On, with the Report recipients separated by commas |
| Recipients may get this data | Tick it to confirm the recipients may receive what the query returns; a report cannot be enabled without it |
The query, with dates passed as parameters (they are never pasted into the text):
SELECT OrderID, CustomerName, OrderDate, Total
FROM dbo.Orders
WHERE OrderDate >= @FromDate
AND OrderDate < @ToDate
ORDER BY OrderDate;
"start of last week" and "start of this week" are worked out on the day of the run, in the machine's time zone, using the week start
you chose. They are dates, so the range is written >= @FromDate AND < @ToDate: that takes all of last week
whatever the time part of OrderDate. Other dates you can use: today, yesterday, start or end of this or last week,
month or year, and "N days ago".
3. Test, then save
Test runs one real check without saving or sending anything, so you see whether the query and parameters work. Save it, and the service picks it up without a restart. Every run is written to the report's run log; if the mail server is down, the same file is sent again after 2, 10 and 30 minutes without running the query again. A Monday missed while the machine was off is sent once when the service starts again, never twice.
Tips
- Only when there are rows. For exception reports ("orders not shipped after 5 days", "jobs that failed") choose Only when the query returns rows, so no news means no email.
- Read-only on purpose. The query must be a single read-only
SELECT; anything that writes is refused, and it runs in a transaction that is always rolled back. On an availability group, the report asks the listener for a readable secondary. - Teams, Slack and SharePoint. The same report can post a summary (the row count and the first rows) to Teams or Slack, and save the file to a SharePoint library or OneDrive folder. A Teams or Slack post cannot carry the file itself.
- File name.
{name}_{date}by default;{date:yyyy-MM}and{time}are available too.
Questions
Can SQL Server email a query result as an Excel file?
Not by itself. Database Mail (sp_send_dbmail) can attach a query result as a text file with a separator such as a comma, which Excel opens as a CSV. A real .xlsx needs another tool, such as SSIS, a PowerShell script with an Excel module, or a reporting tool like NotifyMeWhen.
How do I schedule a report on SQL Server Express?
SQL Server Express has no SQL Server Agent and no Database Mail, so the schedule and the email have to come from outside SQL Server: Windows Task Scheduler running a script, or a service such as NotifyMeWhen that runs the query on its own schedule.
How do I select last week's rows in T-SQL?
Use a half-open range on dates: OrderDate >= the start of last week AND OrderDate < the start of this week. That takes every row from last week whatever its time of day. In NotifyMeWhen you type 'start of last week' and 'start of this week' as parameters and pick the day your week starts on.
Can I skip the email when the query returns nothing?
Yes. In NotifyMeWhen choose 'Only when the query returns rows'. With Database Mail you wrap the call in IF EXISTS (your query) yourself.
Related
- Read SQL Server error logs from several instances
- Compare SQL Agent jobs between two servers
- NotifyMeWhen
- All guides
Sources (Microsoft Learn): sp_send_dbmail, Editions and supported features of SQL Server 2022, Create a schedule (SQL Server Agent).