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 EXISTS logic.

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:

FieldValue for "last week's orders, every Monday"
Server and DatabaseYour SQL Server and the database the query reads
QueryOne 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 onMonday
Schedule0 0 7 ? * MON, Mondays at 07:00 in the machine's local time (the default; Build... picks it for you)
File formatExcel
SendAlways, or Only when the query returns rows
Email the fileOn, with the Report recipients separated by commas
Recipients may get this dataTick 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

Sources (Microsoft Learn): sp_send_dbmail, Editions and supported features of SQL Server 2022, Create a schedule (SQL Server Agent).

An unhandled error has occurred. 🗙