OPENROWSET and Microsoft.ACE.OLEDB.12.0 ERROR

Question

Post reply

OPENROWSET and Microsoft.ACE.OLEDB.12.0 ERROR

Deepak.Sharma507

Ten Centuries

Points: 1225
More actions
May 15, 2012 at 4:59 am

#258266

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

Viewing 15 posts - 1 through 15 (of 16 total)

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

raj acharya SSCrazy Points: 2805 More actions · Answer 1

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

Deepak.Sharma507 Ten Centuries Points: 1225 More actions · Answer 2

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

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

Deepak.Sharma507 Ten Centuries Points: 1225 More actions · Answer 4

Hi Anthony,

Sorry but still no luck. :crying:

I am about to kill myself now...

raj acharya SSCrazy Points: 2805 More actions · Answer 5

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

Deepak.Sharma507 Ten Centuries Points: 1225 More actions · Answer 6

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

Deepak.Sharma507 Ten Centuries Points: 1225 More actions · Answer 7

GOT IT :hehe: 😎 :w00t: 😛 😀 🙂

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

Deepak.Sharma507 Ten Centuries Points: 1225 More actions · Answer 8

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

chandan_jha18 SSChampion Points: 11330 More actions · Answer 9

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

Deepak.Sharma507 Ten Centuries Points: 1225 More actions · Answer 10

Hi Chandan,

Now that I have uninstalled all the components, I installed, I don't see any relevant "provider" when I try to link the excel file.

Even when I installed 32-Bit "Microsoft Access Database Engine 2010" from http://www.microsoft.com/en-us/download/details.aspx?id=13255, I couldn't find anything.

Any help...

Thanks,

Deepak

chandan_jha18 SSChampion Points: 11330 More actions · Answer 11

Deepak.Sharma507 (5/16/2012)
Hi Chandan,
Now that I have uninstalled all the components, I installed, I don't see any relevant "provider" when I try to link the excel file.
Even when I installed 32-Bit "Microsoft Access Database Engine 2010" from http://www.microsoft.com/en-us/download/details.aspx?id=13255, I couldn't find anything.
Any help...
Please follow the link below and let me know if it helps you:
http://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm
With all 64-bit environments coming up these days, you may find difficulty initially to find relevant driver for that. Look at the article above, follow the steps on your test machine first.
Let me know if this helps!
Chandan
Thanks,
Deepak

Deepak.Sharma507 Ten Centuries Points: 1225 More actions · Answer 12

Hi Chandan,

Still no luck.. 🙁

I tried all the steps but getting the same old error.

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.

It ask me to install 64-Bit "Microsoft.ACE.OLEDB.12.0 driver", if I have SQL Server x64 for any Excel version files.

And as I mentioned in my question I have 64-Bit SQL Server 2010 and 32-Bit MS Office. So when I try to install 64-Bit "Microsoft.ACE.OLEDB.12.0 driver" it doesn't allow me to, which is very obvious. And if I install 64-Bit ACE driver forcefully, by using /passive on command prompt, it gave me error every time I try open any Office document (attached earlier)..

Now I have lost all hopes and thinking of a rather dirty workaround. I'll rather push data from EXCEL(VBA), have already lost 2 days around this.

Thanks for your help again.

If any body found any tricks/tips, please do post would love to crack this down though.

Regards,

Deepak

chandan_jha18 SSChampion Points: 11330 More actions · Answer 13

Don't give up yet. I am not able to find a suitable environment for your problem here, but you can keep trying:

http://stackoverflow.com/questions/2899201/microsoft-ace-oledb-12-0-64x-sql-server-and-86x-office

Keep us posted and all the best! I know it frustrates us sometimes but such head-banging days give us golden learning path.

Regards

Chandan

sqlcentral2@mailinator.com SSCommitted Points: 1514 More actions · Answer 14

Thanks very much for this post. This solves the problem exactly.

Deepak.Sharma507 (5/15/2012)
GOT IT :hehe: 😎 :w00t: 😛 😀 🙂
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

4x4 photos