How can we export daily data from SQL Server to Excel and email it

How can we export daily data from SQL Server to Excel and email it
How can we export daily data from SQL Server to Excel and email it

In the daily operations of companies it is often necessary to extract daily data from the database and give it to an Excel.

In these Excels, we usually want to have a specific name along with a date and to be stored in a specific path. It is very useful to email these Excels directly from SQL Server itself.

In the article we will analyze step by step the way and what is required to do all of the above without the use of packages SSIS.

The prerequisites

We should have one pre-installed provider from Microsoft that will allow us to write our data from the database to Excel. We can download the 12 version from here.

Must be the account that raises it service account of SQL Server to have the right to write to the path of these files in the operating system as well sysadmin in the instance.

Finally, we should have run the following in the instance that will allow us to run the T-SQL script to write to Excel:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
EXECUTE sp_configure 'xp_cmdshell', 1;  
GO
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

To see what providers are installed on the instance:

EXEC master.dbo.sp_MSset_oledb_prop

The example

We have a customers table in which we have records. We want to export it to an Excel with title colors and named formats customers_yyyymmdd.xlsx. As a next step we want to send this Excel in an email to a recipient.

So for the example we have only passed 3 entries in the table.

insert into mig_db.dbo.customers
values
('Stratos'),
('Nikos'),
('Giorgos');

select * from mig_db.dbo.customers;
How can we export daily data from SQL Server to Excel and email it
01

To have the Excel formats we want, i.e. Bold, margins, yellow cells, we make a template.xlsx in which we will have done all these formats and saved it in the path where the files will be extracted.

How can we export daily data from SQL Server to Excel and email it
02

The next step is to have our script copy the template to a new file with the name we want the final one to have, e.g. customers_20210101.xlsx.

declare @Sql varchar(200)
set @Sql='COPY C:\Share\template.xlsx C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
print @Sql
exec xp_cmdshell @Sql
GO
How can we export daily data from SQL Server to Excel and email it
03

So we will immediately see that the file was created in our folder which still does not contain records.

How can we export daily data from SQL Server to Excel and email it
04

In the next step, the records will be transferred to Excel. There you should:

  • in the variable @importq to put the query that will read from the table
  • in the variable @dbfile put the path of the file with the name it will have as we did in the previous step with the copy command (C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx')
  • We should change the name in the Excel sheet template to Sheet1 (if it isn't already) otherwise we should change it to the @dbfile variable in select * from [Sheet1$]

USE mig_db
GO
set ANSI_NULLS ON
GO
set QUOTED_IDENTIFIER ON
GO
declare @dbfile varchar(2000), @importq varchar(2000)
set @importq = 'select * from dbo.customers'
set @dbfile = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Excel 12.0;Database=C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'''+','+'''select * from [Sheet1$]'''+')'+@importq
print @dbfile
exec(@dbfile)
go
How can we export daily data from SQL Server to Excel and email it
05

That was it, if we now open Excel we will see the records with the formatting we want.

How can we export daily data from SQL Server to Excel and email it
06

In previous article we had seen how to activate it Database Mail in SQL Server. After doing these steps with the following script we can send the Excel by Email as well.

declare @dbfile2 varchar(1000)
set @dbfile2 = 'C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'gmail',  
@recipients = 'account@dataplatform.gr',  
@subject = 'Customers Report',  
@body = 'Here is the daily customer report.',
@file_attachments = @dbfile2;
print @dbfile2

So if everything goes well, the Email will come as shown in the image below.

How can we export daily data from SQL Server to Excel and email it
07

In summary the code:

declare @Sql varchar(200)
set @Sql='COPY C:\Share\template.xlsx C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
print @Sql
exec xp_cmdshell @Sql
GO

set ANSI_NULLS ON
GO
set QUOTED_IDENTIFIER ON
GO
declare @dbfile varchar(2000), @importq varchar(2000)
set @importq = 'select * from mig_db.dbo.customers'
set @dbfile = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Excel 12.0;Database=C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'''+','+'''select * from [Sheet1$]'''+')'+@importq
print @dbfile
exec(@dbfile)
go

declare @dbfile2 varchar(1000)
set @dbfile2 = 'C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'gmail',  
@recipients = 'account@dataplatform.gr',  
@subject = 'Customers Report',  
@body = 'Here is the daily customer report.',
@file_attachments = @dbfile2;
print @dbfile2

And if I want this whole process to happen automatically every day at a certain time?

We can just make one Job to SQL Server Agent.

The process is to right click New, Job... , Steps, New..., give a name to the step, select the base that has the data and add the sql scripts we created before, press OK and finally on the tab Schedules to define when it will run.

How can we export daily data from SQL Server to Excel and email it
08

Is there an alternative more direct way?

In case we don't care about the appearance of Excel with bold titles, colors, etc., we don't care about keeping the reports automatically and giving them a name with the date dynamically, then we can during the execution of the procedure that sends the msdb.dbo email.sp_send_dbmail define directly the query we want to execute and attach it as an attachment as in the previous way:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'gmail',
@recipients = 'account@dataplatform.gr',
@query = '
set nocount on;
USE mig_db;
go
select * from dbo.customers
go',
@subject = 'Customers Report',
@body = 'Here is the daily customer report.',
@query_attachment_filename='customers.xls',
@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,
@attach_query_result_as_file = 1;
Share it

Leave a reply