none
Cannot create instance of "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" on x64 Vista with x64 SQL Server 2008

    Question

  • I am attempting to take data from an Excel 2007 spreadsheet and use OpenDataSource to throw the data into a SQL Server 2008 SP1 table under an x64 OS and x64 SQL Server.  I was able to get this to work under x86 versions of the OS and SQL by setting sp_MSset_oledb_prop with values of AllowInProcess and DynamicParameters to 1.  However, under x64 SQL 2008, when I set the AllowInProcess option to 1, I get the following error:  "OLEDB provider "Microsoft.ACE.OLEDB.12.0" cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode."  If I turn this flag off, I receive the error shown above in the title.

    I saw suggestions about creating a .Net program and then forcing it to target an x86 processor, but this method did not work either.  I also saw suggestions about installing the 2007 Data Access drivers, and although I believe they already were installed, I installed them specifically, but it changed nothing.  I also tried remotely accessing the x64 system from an x86 environment, but it also changed nothing.

    Therefore, I cannot get the OpenDataSource method to work from Mgmt Studio or from an x86 program, so I would appreciate insights into how someone is supposed to be able to retrieve data from an Excel 2007 workbook in an x64 environment.  I find it hard to believe that with numerous servers running on x64 that some method does not exist.
    Friday, August 21, 2009 2:39 AM

Answers

  • Everyone so far is fairly correct.  This provider is not released for 64 bit and so you need a 32 bit process to host it.  Your scenario appears to be a linked server scenario as well.  To put the pieces together, you have the following scenario:

    1.) 64 bit Windows Operating System
    2.) 64 bit SQL Server installed
    3.) Linked server to <insert office provider here>

    Unfortunately, since there is no 64 bit provider, you cannot create linked servers directly to these data sources through (64 bit) SQL Server.  From any 64 bit process, these providers are not available.

    There are 3 workarounds, ranging in complexity:

    Option 1:
    Use 32 bit SQL Server on the 64 bit machine.  It will now be able to see all the 32 bit providers.  Until provider vendors begin to support 64 bit, this offers the highest level of interactivity with a minimum of labor / maintenance cost.  However, it also comes with a performance penalty as SQL Server will be running in WoW mode on the 64 bit server.

    Option 2:
    Build a bridge out of SQL Express.  Keep your main SQL Server instance 64 bit, but also install SQL Express 32 bit Side by Side.  Then when you need to use 32 bit providers, create the linked server on the SQL Express instance and link to that from the main instance.  Your linked server pathway would go SQL Server 64 bit -> SQL Server 32 bit (over 64 bit client) -> (32 bit client) for linked server X.  This has less performance cost, but more maintenance cost.  Still no development costs, though, outside of configuring the linked servers.

    Option 3:
    Reverse the flow of data from pull to push.  Instead of having SQL Server pull data from the source that only supports 32 bit clients, push data from that source to SQL Server (as it supports 32/64 bit clients).  For example: instead of having SQL Server pull data from an Access file, include macros in the Access file to connect to SQL Server and push the data up to the server.  Just don't do this one.  It is very expensive and makes it impossible to completely migrate, also often requires human intervention to perform the actual push.

    I have suggested these 3 options in one way or another on these forums quite a few times, and almost always see people go for Option 1, though a few have taken Option 2 if that provides some sense of where people in general are currently leaning when they see this problem.


    I hope that helps,

    John

    This post is provided 'as is' and confers no express or implied warranties or rights.
    Thursday, September 10, 2009 9:46 PM

All replies

  • Have you considered creating a linked server to the excel datasource and then querying the data. The following links should help in that regard

    http://support.microsoft.com/kb/306397/EN-US/

    http://support.microsoft.com/kb/321686

    Hope this helps
    This posting is provided "AS IS" with no warranties, and confers no rights
    Wednesday, September 09, 2009 11:36 PM
  • Hi,

    Excel 2007 can be read only in X86 environment. you must configure your VS solution to X86. I've managed to read excel files in this way with the following code snippet:

    string sourceConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\sample.xlsx; Extended Properties=Excel 12.0;";
    OleDbConnection connection = new OleDbConnection(connectionString);
                OleDbCommand command = new OleDbCommand();
                OleDbDataReader reader;
                try
                {
                    connection.Open();
                    command.Connection = connection;
                    command.CommandType = CommandType.TableDirect;
                    command.CommandText = sheet;
                    reader = command.ExecuteReader();
    
    .
    .
    .


    If it is not working neither, I would go with Raj's idea.
    I hope it helps.
    Janos
    A train station is where a train stops. On my desk I have a workstation :)
    Thursday, September 10, 2009 9:45 AM
  • Everyone so far is fairly correct.  This provider is not released for 64 bit and so you need a 32 bit process to host it.  Your scenario appears to be a linked server scenario as well.  To put the pieces together, you have the following scenario:

    1.) 64 bit Windows Operating System
    2.) 64 bit SQL Server installed
    3.) Linked server to <insert office provider here>

    Unfortunately, since there is no 64 bit provider, you cannot create linked servers directly to these data sources through (64 bit) SQL Server.  From any 64 bit process, these providers are not available.

    There are 3 workarounds, ranging in complexity:

    Option 1:
    Use 32 bit SQL Server on the 64 bit machine.  It will now be able to see all the 32 bit providers.  Until provider vendors begin to support 64 bit, this offers the highest level of interactivity with a minimum of labor / maintenance cost.  However, it also comes with a performance penalty as SQL Server will be running in WoW mode on the 64 bit server.

    Option 2:
    Build a bridge out of SQL Express.  Keep your main SQL Server instance 64 bit, but also install SQL Express 32 bit Side by Side.  Then when you need to use 32 bit providers, create the linked server on the SQL Express instance and link to that from the main instance.  Your linked server pathway would go SQL Server 64 bit -> SQL Server 32 bit (over 64 bit client) -> (32 bit client) for linked server X.  This has less performance cost, but more maintenance cost.  Still no development costs, though, outside of configuring the linked servers.

    Option 3:
    Reverse the flow of data from pull to push.  Instead of having SQL Server pull data from the source that only supports 32 bit clients, push data from that source to SQL Server (as it supports 32/64 bit clients).  For example: instead of having SQL Server pull data from an Access file, include macros in the Access file to connect to SQL Server and push the data up to the server.  Just don't do this one.  It is very expensive and makes it impossible to completely migrate, also often requires human intervention to perform the actual push.

    I have suggested these 3 options in one way or another on these forums quite a few times, and almost always see people go for Option 1, though a few have taken Option 2 if that provides some sense of where people in general are currently leaning when they see this problem.


    I hope that helps,

    John

    This post is provided 'as is' and confers no express or implied warranties or rights.
    Thursday, September 10, 2009 9:46 PM
  • John,
    This is VERY helpful!  Regarding Option 2, rather than set up the 32-bit linked server on the same physical machine using Express, would it be possible to set it up on another machine (VM or physical) using Express, assuming one were available?  Although I am currently running Vista, I will end up running on Windows 7 or Server 2008, so VM options might be a way to handle this too, if the import from Excel does not have to be performed constantly.  Thank you again for the information, Jeff

    Thursday, September 10, 2009 11:02 PM
  • I am getting the same error, but I am running with XP, not Vista. The server, however, is running on a 64 bit operating system.  I'm not sure if this makes a difference.  However, I do not have access to make changes in the version of SQL server running on the server, and I don't know whether options 1 or 2 can be implemented on the client. 
    Wednesday, September 15, 2010 3:05 PM
  • I just talked to the software architect.  He says Option 1 is not going to happen, but option 2 is a possibility.

     

    Wednesday, September 15, 2010 3:17 PM
  • I had similar problem and found a workaround.

    Problem: trying to open an Excel spreadsheet from a stored procedure, got "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered". Of course that provider was registered.

    Facts:

    1. Server's OS - 64 bit Windows (server 2003 x64)

    2. SQL server - 64 bit (SQL 2008 Developer Edition x64)

    3. to access the excel file (using distributed query) I was using:

    OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=1;Database=[excel file path].......

    Then my collueague found this: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

    I have installed the AccessDatabaseEngine_x64.exe and changed the connection string to:

    OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;IMEX=1;
     Database=[excel file path].......

    And it works fine. The only problem is that you cannot install the package (AccessDatabaseEngine_x64.exe ) if there is a MS Office 32 bit product installed on that machine. Trying installing AccessDatabaseEngine.exe (32 bit version) will NOT help you in this scenario.

    Haven't tried yet if you have MS Office 2010 (x64) installed on the server. Supposedly it should provide a 64 bit Jet OLEDB provider.

    • Proposed as answer by Gabor Lehotai Thursday, January 27, 2011 10:52 AM
    Thursday, December 23, 2010 5:05 PM
  • I'm trying to implement option 2, which seems pretty straightforward.

    So now I have:


    MSSQL64 -- Main server 2008 r2 x64, with a remote server configured to:

    MSSQL32 -- The extra named instance, 2008 r2 x86, with a remote server configured to

    ACCESSLINK -- The access data file.

     

    I can query ACCESSLINK tables from MSSQL32.

     

    I can query other databases on MSSQL32 from MSSQL64.

     

    But, try as I might, I can't figure out how to query ACCESSLINK from MSSQL64.

    In fact, only the actual MSSQL32's actual databases show up in the object explorer, not the linked servers.  (The tables in ACCESSLINK do show up in the object explorer in MSSQL32).

     

    Any help?

    Wednesday, January 26, 2011 9:02 PM
  • Thanks, gp_msdn. Your solution seems to be working. Cool!

    • Proposed as answer by misdean Monday, February 28, 2011 6:27 PM
    • Unproposed as answer by misdean Monday, February 28, 2011 6:27 PM
    Thursday, January 27, 2011 10:54 AM
  • ever solve the option dilemma?

    Da Lizard
    Monday, May 09, 2011 7:30 PM
  • i am having the same problem with sql express 2008r2

    i am interested in your option 2, but how can you configure it?

    in the 2008r2x32, i created a link server connected to the excel file (tested by: select * from [excelfile]...[sheet$])

    how can i create a link server in 2008r2x64 , and how can i reference to this ?

    something like ?:  select * from [2008r2x64]...[excelfile]...[sheet$]

    but give something max prefix is 3.

    Tuesday, July 03, 2012 7:00 PM
  • I installed AccessDatabaseEngine_x64.exe in my Dev box which has Window 2008 R2 but still no luck so far.

    Please help ASAP. Thank you

    Sunday, January 12, 2014 6:34 PM