How do I know which ole provider is available for SQL Server 2008?

Question

I am trying to access an Excel file in MSMS. After searching the Internet, I could not get it to work.

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) 

• Try changing the configuration for OLE, for example:

   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 

• Run request:

   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]') 

In both cases, I received 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 the SQL server and 10 providers start the sp system by default:

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

How to solve this problem? How to find out if MICROSOFT.ACE.OLEDB.12.0 or MICROSOFT.JET.OLEDB.4.0 for SQL Server?

Answer 1

For a file type with the extension .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"