OPENROWSET and Microsoft.ACE.OLEDB.12.0 ERROR

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » OPENROWSET and Microsoft.ACE.OLEDB.12.0 ERROR...

17 posts, Page 1 of 2

1 2 »»

OPENROWSET and Microsoft.ACE.OLEDB.12.0 ERROR

Rate Topic

Display Mode

Topic Options

Author

Message

Deepak.Sharma507

Posted Tuesday, May 15, 2012 4:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:20 AM
Points: 35, Visits: 114

Hi all,

Hope you all are doing fine... (unlike me..)..

I am trying to run the following query

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\users\USER\desktop\book.xlsx', 'SELECT * FROM [Sheet1$]');

and getting the following error

Msg 7308, Level 16, State 1, Line 1
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.

I followed this http://blog.hoegaerden.be/2010/03/29/retrieving-data-from-excel/
and this http://www.mytechmantra.com/LearnSQLServer/Troubleshoot_OLE_DB_Provider_Error_P1.html
and this http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-provider-is-used-to-run-in-apartment-mode/
but none solved my problem.

I am running

Windows 7
SQL 2008 64Bit
Office 2010 32bit.

I am banging my head over this issue from last two days now.. Any help or guidance would really help.

Thanks for your time.

Regards,
Deepak

Post #1300136

raj acharya

Posted Tuesday, May 15, 2012 6:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:07 AM
Points: 186, Visits: 343

hi,

if you have already configure then try this way to read data from excel

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\users\USER\desktop\book.xlsx;IMEX=1',
'SELECT * FROM [Sheet1$]')

check your path where file located once.

Raj Acharya

Post #1300158

Deepak.Sharma507

Posted Tuesday, May 15, 2012 6:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:20 AM
Points: 35, Visits: 114

Thanks for your time and reply Raj but unfortunately got the same error

Msg 7308, Level 16, State 1, Line 1
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.

Regards,
Deepak

Post #1300160

anthony.green

Posted Tuesday, May 15, 2012 6:20 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

could it be that its using parallelism and using more than 1 thread. try putting OPTION (MAXDOP 1) at the end of your query to force single-threading

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1300163

Deepak.Sharma507

Posted Tuesday, May 15, 2012 6:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:20 AM
Points: 35, Visits: 114

Hi Anthony,

Sorry but still no luck. Crying

I am about to kill myself now...

Post #1300164

raj acharya

Posted Tuesday, May 15, 2012 6:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:07 AM
Points: 186, Visits: 343

can you run sp_configure
and tell us what values right now showing for Ad Hoc Distributed Queries
in minimum, maximum ,config_value ,run_value

Raj Acharya

Post #1300168

Deepak.Sharma507

Posted Tuesday, May 15, 2012 7:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:20 AM
Points: 35, Visits: 114

Hi Raj,

This is what I get when I ran sp_configure.

name : Ad Hoc Distributed Queries
minimum : 0
maximum : 1
config_value : 1
run_value : 1

Post #1300214

Deepak.Sharma507

Posted Tuesday, May 15, 2012 8:38 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:20 AM
Points: 35, Visits: 114

GOT IT

Got the tip from this link
http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/

Installed the "Microsoft Access Database Engine 2010 64 bit" (which I did earlier as well), but by running it passively ie. I ran it from command prompt using Admin rights and using the command

"AccessDatabaseEngine_x64 /passive".

This did the trick and I can now successfully select the records from excel file using following query.

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0; Database=C:\book2.xlsx', 'SELECT * FROM [Sheet1$]');

Thanks all for you help and time.

Regards,
Deepak

Post #1300310

Deepak.Sharma507

Posted Wednesday, May 16, 2012 4:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:20 AM
Points: 35, Visits: 114

Sad again

Everything was looking fine until I opened a doc file. I received the attached error whenever I am trying to open any MS Office document (xlsx, docx etc.)

Any help...

Thanks,
Deepak