How to find which OLE provider is available for SQL Server?

41,843

For file type with extention .xlsx use 'Excel 12.0' or 'Excel 12.0 Xml' instead of Excel 9.0

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

If you are connecting to Microsoft Office Excel data, add the appropriate Extended Properties of the OLEDB connection string based on the Excel file type:

File Type (extension)                               Extended Properties
---------------------------------------------------------------------------------
Excel 97-2003 Workbook (.xls)                       "Excel 8.0"
Excel 2007-2010 Workbook (.xlsx)                    "Excel 12.0 Xml"
Excel 2007-2010 Macro-enabled workbook (.xlsm)      "Excel 12.0 Macro"
Excel 2007-2010 Non-XML binary workbook (.xlsb)     "Excel 12.0"
Share:
41,843
KentZhou
Author by

KentZhou

Senior Developer at ACTRA since 2005. Master Degree of Computing Skills: Database: Admin, Design/Data Model, Development(SQL), ... Web: ASP.NET, MVC, Java, ... C#,VB,java, JavaScript,Power Builder, ... Silverlight Line of Business Application System Design & Architecture

Updated on March 30, 2020

Comments

  • KentZhou
    KentZhou about 4 years

    I try to access an Excel file in SSMS. After searching the internet, I could not get it working.
    Here is what I did:

    My environment:

    Windows 7(64bit) SP 1, 
    Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   
    Office 2010 Pro Plus with Access installed(32 bit)
    
    1. Try to change config for OLE like:

      exec sp_configure 'Advanced', 1
      RECONFIGURE
      
      exec sp_configure 'Ad Hoc Distributed Queries', 1
      RECONFIGURE
      
      EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1  
      EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
      
    2. Run query:

      SELECT * FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Text;Database=C:\Temp\;','SELECT * FROM [test.csv]')
      

      or

      SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [test.csv]')
      

    For both cases, I got an error message like:

    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.
    

    or

    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.
    

    Then I checked the linked server on SQL server, and there are 10 providers by default by run system sp:

    EXEC master.dbo.sp_MSset_oledb_prop 
    
    SQLOLEDB
    MSOLAP
    SQLNCLI11
    ADsDSOObject
    SQLNCLI
    SQLNCLI10
    Search.CollatorDSO
    MSDASQL
    MSDAOSP
    MSIDXS
    

    How to resolve this problem? How do I know if MICROSOFT.ACE.OLEDB.12.0 or MICROSOFT.JET.OLEDB.4.0 is available for SQL Server?

  • Prahalad Gaggar
    Prahalad Gaggar about 11 years
    Not working in My Case , Same problem.Do you have any other solution?
  • Aleksandr Fedorenko
    Aleksandr Fedorenko about 11 years
    @Luv Could you clarify that, please?Error, your provider, etc
  • Prahalad Gaggar
    Prahalad Gaggar about 11 years
    Search over google for 2 hours or so. Error Msg 7308, Level 16, State 1, Line 39 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.
  • Aleksandr Fedorenko
    Aleksandr Fedorenko about 11 years
    @Luv What versions SQLServer(32 or 64 bit) and Microsoft Access Database Engine 2010 (32 or 64 bit)?
  • Prahalad Gaggar
    Prahalad Gaggar about 11 years
    SQL server 64 Bit and Microsoft Access Database Engine 2010 64 Bit
  • Aleksandr Fedorenko
    Aleksandr Fedorenko about 11 years
    For a 64bit build of SQL to use the Access drive you'll need to install the 64bit version of the Access drive. If you have the 32bit version of Office installed you'll need to either remove that and install the 64bit version of office or uninstall the 64bit build of SQL and install the 32bit build.