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.
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
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)
select @Provider = 'Microsoft.Jet.OLEDB.4.0' ; select @ExcelString = 'Excel 8.0;Database=' + @ReportFile + ';HDR=YES';
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.
Comments (2)
Commented:
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.
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:just to update this, Microsoft has now a 64 bit driver :
http://www.microsoft.com/download/en/details.aspx?id=13255
Thanks
Jose