OPENROWSET and Microsoft.ACE.OLEDB.12.0 ERROR

  • 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

  • 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

  • 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

  • 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

  • Hi Anthony,

    Sorry but still no luck. :crying:

    I am about to kill myself now...

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

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

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