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!
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\templat
e.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
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)
: 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';