OPENROWSET issue with 64bit vs 32bit

Question

Post reply

OPENROWSET issue with 64bit vs 32bit

Joe-420121

Hall of Fame

Points: 3152
More actions
July 10, 2012 at 6:03 am

#263499

I have windows 7-64bit/ SQL Server 2008 R2-64bit; but my MS office is 32 bit/ ODBC Drivers 32 bit.
I have a situation to use OPENROWSET function as below:
SELECT * From OpenRowset('Microsoft.Jet.OLEDB.4.0',';Database=C:\data\Testdb.mdb;','SELECT * from table1') as b
But I get error msg as below:
“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.”
I do have 1 for ‘Ad Hoc Distributed Queries'.
I have been working on this issue last 2 days using many online tips, but no luck. Pls help.
Thanks in advance
July 10, 2012 at 7:16 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 1

MS office 2007 32 bit, is installed.

Could you give more detail; what SQL Express engine? do i have to use SQL express..I have deverloper 2008 R2 64bit

Lowell SSC Guru Points: 323518 More actions · Answer 2

try openrowset like this, using the aCe driver;

this is a tested working example for me:

SELECT * FROM OPENROWSET('MSDASQL',

'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);

UID=admin;

UserCommitSync=Yes;

Threads=3;

SafeTransactions=0;

ReadOnly=1;

PageTimeout=5;

MaxScanRows=8;

MaxBufferSize=2048;

FIL=excel 12.0;

DriverId=1046;

DefaultDir=C:\Data\BlockGroups_2010;

DBQ=C:\Data\BlockGroups_2010\AKblockgroup.xls',

'SELECT * FROM [AK$]')

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\Data\BlockGroups_2010\AKblockgroup.xls',

'SELECT * FROM [AK$]')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 3

lowell,

I get err msg respectively:

-----------------------------------------------

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

-----------------------------------------------

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

-----------------------------------------------

thanks for your hlep

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 4

lowell,

where is aCe driver?

I understand what is ACE driver, I too installed it from

http://www.microsoft.com/en-us/download/details.aspx?id=23734

thanks

Lowell SSC Guru Points: 323518 More actions · Answer 5

Joe-420121 (7/10/2012)
lowell,
I get err msg respectively:
-----------------------------------------------
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
-----------------------------------------------
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
-----------------------------------------------
thanks for your hlep

i meant the ACE driver, sorry about the case thing; i have to watch my fingers type, and not the screen 🙂

based on the first error, I'm thinking the error is in the path to the file itself. Data source name not found to me means bad path to teh file?

For the second error message, it looks like the ACE drivers are already installed; I would have to google the error message to track down why it failed.

the drivers are on the microsoft site for reference;

http://www.microsoft.com/en-us/download/details.aspx?id=13255

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 6

lowell,

could you tell me your enviroment?

what is windows version? 32bit or 64bit?

what is sql version ? 32 bit or 64 bit?

what is MS access version ? 32bit or 64bit?

what is ODBC version ? 32bit or 64bit?

thanks