How to find which OLE provider is available for SQL Server?
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"
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, 2020Comments
-
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)
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
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
orMICROSOFT.JET.OLEDB.4.0
is available for SQL Server? -
Prahalad Gaggar about 11 yearsNot working in My Case , Same problem.Do you have any other solution?
-
Aleksandr Fedorenko about 11 years@Luv Could you clarify that, please?Error, your provider, etc
-
Prahalad Gaggar about 11 yearsSearch 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 about 11 years@Luv What versions SQLServer(32 or 64 bit) and Microsoft Access Database Engine 2010 (32 or 64 bit)?
-
Prahalad Gaggar about 11 yearsSQL server 64 Bit and Microsoft Access Database Engine 2010 64 Bit
-
Aleksandr Fedorenko about 11 yearsFor 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.