<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Exporting Content From SQL Server to Excel using OPENROWSET

Published on
12,054 Points
5,854 Views
2 Endorsements
Last Modified:
Approved
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
Author:perezjos
2 Comments
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.
0
LVL 5

Author Comment

by:perezjos
Hi,

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

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

Thanks
Jose
0

Featured Post

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

See the Basics of Office 365's Note Taking app, OneNote
Watch this simple and effective video tutorial to extract attachments from Outlook 2007 and try this easy method by yourself. No need to go anywhere, just watch the video and export attachments from Outlook in few simple steps. To know more, click h…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month