<

Exporting Content From SQL Server to Excel using OPENROWSET

Published on
12,288 Points
6,088 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
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free