Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

OPENROWSET and Microsoft.ACE.OLEDB.12.0 ERROR Expand / Collapse
Author
Message
Posted Tuesday, May 15, 2012 4:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, May 15, 2012 6:12 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, May 15, 2012 6:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, May 15, 2012 6:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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
Posted Tuesday, May 15, 2012 6:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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.
I am about to kill myself now...
Post #1300164
Posted Tuesday, May 15, 2012 6:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, May 15, 2012 7:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, May 15, 2012 8:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, May 16, 2012 4:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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




  Post Attachments 
Error.png (35 views, 16.75 KB)
Post #1300902
Posted Wednesday, May 16, 2012 5:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 408, Visits: 1,666
I have used linked server to read the data from excel and found it to be easy. Will that method work for you?

Thanks
Chandan
Post #1300926
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse