Exporting Content From SQL Server to Excel using OPENROWSET

Published:
Updated:
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package?

The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.  

Background:
I have tested this on a SQL Server 2008 R2 system running on a VMWare Windows 2008 machine.  When I first started I tried to use the JET OLE DB provider, but I did not manage to make it work.  I was always having the same error :
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
So, I downloaded and installed on my SQL Server the Office 2007 Drivers and used that instead of JET.

The Office System Drivers proved to be perfect to transfer data between the supported file formats and SQL Server!

Example Export:
 
First you need to create an Excel  template that has the structure of the data you want to export.  For example, I have created an Excel file with two columns: Name and Date.  On my system, I have saved this file as D:\ETLsFiles\Excel\template.xls, which I will refer to below.
Template.xls

As we are going to use a copy of the above file, we need to be sure that SQL Server has been enabled to use xp_cmdshell and we have access to Ad Hoc Distributed Queries.  Here is script to do so:

USE [master] GO sp_configure 'show advanced options',1 GO RECONFIGURE WITH OVERRIDE GO sp_configure 'xp_cmdshell',1 reconfigure GO sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure Go

Open in new window


The script below will copy the template as a new file, use the current date as the file name, and insert rows of data from SQL Server in the new file created.  You may play with the variables and the queries to fit your needs.

declare @ReportTemplate varchar(255); declare @ReportFile varchar(255); declare @query varchar(1000),@sqlstmt varchar(1000); declare @Provider varchar(255), @ExcelString varchar(255),@cmd varchar(255); select @Provider = 'Microsoft.ACE.OLEDB.12.0'; select @query='SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs' ; select @ReportFile ='D:\ETLsFiles\Excel\' + convert(char(8), getdate(), 112) + '.xls' select @ExcelString = 'Excel 12.0;Database=' + @ReportFile + ';HDR=YES'; select @ReportTemplate='D:\ETLsFiles\Excel\template.xls'; -- replace your path here select @cmd = 'copy "' + @ReportTemplate + '" "' + @ReportFile + '"' exec master..xp_cmdshell @cmd, NO_OUTPUT select @sqlstmt = 'insert into OPENROWSET(''' + @provider + ''',''' + @ExcelString + + ''',''SELECT Name, Date FROM [Sheet1$]'')' + @query; exec(@sqlstmt)

Open in new window


Note: You may try to make it also work with JET OLE DB 4.0 provider by replacing the contents of the two variables below:

select @Provider = 'Microsoft.Jet.OLEDB.4.0' ; select @ExcelString = 'Excel 8.0;Database=' + @ReportFile + ';HDR=YES';

Open in new window

2
6,549 Views

Comments (2)

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Nice work!  

Note for 64-bit SQL Server that since the ACE drive is 32-bit, that you will often get a similar error message to what you got for JET.

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

If you worked around this, it would be interesting to learn how.  As far as I know though, it will work fine for 32-bit SQL running on 64-bit Windows.  

Anyway, thank you for sharing.  You have my Yes vote above.

Author

Commented:
Hi,

just to update this, Microsoft has now a 64 bit driver :

http://www.microsoft.com/download/en/details.aspx?id=13255

Thanks
Jose

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.