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

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
• How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024

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;

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.

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

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

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

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

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.

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.

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;