{"id":2884,"date":"2023-02-15T07:00:00","date_gmt":"2023-02-15T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2884"},"modified":"2025-07-20T13:52:30","modified_gmt":"2025-07-20T10:52:30","slug":"pos-mporoyme-na-exagoyme-kathimerina-d","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-exagoyme-kathimerina-d\/","title":{"rendered":"How can we export daily data from SQL Server to Excel and email it"},"content":{"rendered":"

In the daily operations of companies it is often necessary to extract daily data from the database and give it to an Excel.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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<\/a>.<\/p>\n\n\n\n

The prerequisites<\/h2>\n\n\n\n

We should have one pre-installed provider <\/strong>from Microsoft that will allow us to write our data from the database to Excel. We can download the 12 version from here<\/a>.<\/p>\n\n\n\n

Must be the account that raises it service account <\/strong>of SQL Server to have the right to write to the path of these files in the operating system as well sysadmin <\/strong>in the instance.<\/p>\n\n\n\n

Finally, we should have run the following in the instance that will allow us to run the T-SQL script to write to Excel:<\/p>\n\n\n\n

sp_configure 'show advanced options', 1;\nGO\nRECONFIGURE;\nGO\nsp_configure 'Ad Hoc Distributed Queries', 1;\nGO\nRECONFIGURE;\nGO\nEXECUTE sp_configure 'xp_cmdshell', 1;  \nGO\nRECONFIGURE;\nGO\nEXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1\nGO\nEXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1\nGO\n<\/pre>\n\n\n\n
To see what providers are installed on the instance:<\/p>\n\n\n\n
EXEC master.dbo.sp_MSset_oledb_prop<\/pre>\n\n\n\n
The example<\/h2>\n\n\n\n
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<\/em>. As a next step we want to send this Excel in an email to a recipient.<\/p>\n\n\n\n
So for the example we have only passed 3 entries in the table.<\/p>\n\n\n\n
insert into mig_db.dbo.customers\nvalues\n('Stratos'),\n('Nikos'),\n('Giorgos');\n\nselect * from mig_db.dbo.customers;<\/pre>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
To have the Excel formats we want, i.e. Bold, margins, yellow cells, we make a template.xlsx<\/em> in which we will have done all these formats and saved it in the path where the files will be extracted.<\/p>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
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<\/em>.<\/p>\n\n\n\n
declare @Sql varchar(200)\nset @Sql='COPY C:\\Share\\template.xlsx C:\\Share\\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'\nprint @Sql\nexec xp_cmdshell @Sql\nGO<\/pre>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
So we will immediately see that the file was created in our folder which still does not contain records.<\/p>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
In the next step, the records will be transferred to Excel. There you should:<\/p>\n\n\n\n