November 9, 2011 at 2:39 pm
Hi everyone:
I'm trying to export some tables to some Sheets (same XLSX or XLS file). The problem is that following code doesn't work in 64bit system.
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [Det$]') select * from #details
go
In example:
Source / Destination: filename[sheet name]
Table A: #details / testing.xls[det]
Table B: #names / testing.xls[nam]
...
and goes on...
Error shown:
Mens. 7308, Nivel 16, Estado 1, Línea 3
El proveedor OLE DB 'Microsoft.Jet.OLEDB.4.0' no puede usarse para consultas distribuidas porque está configurado para ejecutarse en el modo de subprocesamiento controlado simple.
(sorry it's in Spanish)
I tryed setting the following:
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
but it didn't work... (same error message)
I tryed on a 32bit Win7 and it worked fine, I'm wondering if anyone solved this compatibility issue.
My Configuration:
Windows 7 Ultimate (64bit)
Ms Office 2010 (32bit)
AccessDatabaseEngine_X64 (installed)
Microsoft SQL server 2008R2
Microsoft SQL Server Management Studio:10.0.4064.0
Microsoft Data Access Components (MDAC): 6.1.7601.17514
Microsoft MSXML: 3.0 4.0 5.0 6.0
Microsoft Internet Explorer: 9.0.8112.16421
Microsoft .NET Framework: 2.0.50727.5448
Sistema operativo: 6.1.7601
Thanks a lot for your help
M.
November 9, 2011 at 4:47 pm
I would try a different provider. I try to use the Native Client, figuring it should be the most up to date.
November 14, 2011 at 1:56 am
This was removed by the editor as SPAM
November 17, 2011 at 6:04 am
I think the way to do this would be to create a 32 bit DSN and then use a linked server to do the export, as I know 64 bit SSIS doesnt like running excel in 64 bit mode so you have to tell it run 32 bit.
if not an SSIS package will do the job, just remember to set 64 bit runtime to false in the solution properties, I yet again fell into that trap yesterday.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply