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

Question

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?

Yean

OLE DB 4.0 is not compatible with 64 bit. What version AccessDatabaseEngine you're using? – Taradiddle

I used Office 2010 32bit. then I changed to Office 2010 64bit, it seems work with excel xls file like: select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\Temp\Test.xls;', 'SELECT * FROM [Location1$]'). But it not work with excel xlsx file like: select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 9.0;Database=C:\Temp\Test.xlsx;', 'SELECT * FROM [Location1$]'). How to make it work with xsls file? what's proper syntax for OpenRowSet for this case? – Yean

Answer 1

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"

Taradiddle

Not working in My Case , Same problem.Do you have any other solution? – Dogleg

@Luv Could you clarify that, please?Error, your provider, etc – Taradiddle

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. – Dogleg

@Luv What versions SQLServer(32 or 64 bit) and Microsoft Access Database Engine 2010 (32 or 64 bit)? – Taradiddle

SQL server 64 Bit and Microsoft Access Database Engine 2010 64 Bit – Dogleg

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. – Taradiddle