OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

_{Monday, July 23, 2012 10:43 AM}

Hi,

I have found a bit of info for this on the web already, but all looks a bit painful.  If so, then fine but thought I would post first in case someone has an easy fix.

Environment:
Win Srvr 2008 R2 + SQL Srv 2008 R2 + Office 2010 (all x64)
Win Vista Business + SSMS 2008R2 + Office 2010 (all x64)

From my Vista desktop I am wanting to import data from Excel to SQL using OPENROWSET like so:
SELECT * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;Database=G:\ADMINCUR.xls',
    'SELECT * FROM [OPEN ORDERS & CURRENT MONTH INV$]')

I get the error in the title.

I have downloaded the "Microsoft Access database engine 2010 (x64)" and installed it on the server.  I will aslo install on my desktop.  I have already tried this to no avail:
sp_configure ``'show advanced options'``, ``1``;
GO
RECONFIGURE``;
GO
sp_configure ``'Ad Hoc Distributed Queries'``, ``1``;
GO
RECONFIGURE``;
GO

PLEASE HELP!!!  I am on a bit of a deadline and this is a major drag.

Thanks,

Charles

_{Tuesday, July 24, 2012 8:44 AM ✅Answered | 2 votes}

Hi Charles,

Please refer to this blog:

Accessing Excel files on a x64 machine
http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx

This blog describes how to use Microsoft.ACE.OLEDB.12, and the scenario is similar with yours.

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

_{Monday, July 23, 2012 11:27 AM}

Hi,

Update - since installing "Microsoft Access database engine 2010 (x64)" I get this and can't get rid of it:
OLE DB provider
"Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could
not find installable
ISAM.".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider
"Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Query:
SELECT FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 9.0; HDR=YES; IMEX=1; Database=G:\ADMINCUR.xls',
'SELECT * FROM [OPEN ORDERS & CURRENT MONTH INV$]')

Thanks,

Charles

_{Tuesday, July 24, 2012 10:40 AM}

Absolute star!!!  Tried so many articles to no avail.

One other thing, I was running this from the SSMS on my desktop and the path I was using was relative to my local desktop.  What I need to be doing is running the query with the path set as from the server's perspective.  The link above was the real answer though.

Thanks again.