Exporting Content From SQL Server to Excel using OPENROWSET

Published on
11,655 Points
2 Endorsements
Last Modified:
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.  

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.

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

LVL 61

Expert Comment

by:Kevin Cross
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 Comment


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



Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Join & Write a Comment

How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month