如何查找可用于 SQL Server 的 OLE 提供程序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15434748/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find which OLE provider is available for SQL Server?
提问by KentZhou
I try to access an Excel file in SSMS. After searching the internet, I could not get it working.
Here is what I did:
我尝试访问 SSMS 中的 Excel 文件。在互联网上搜索后，我无法让它工作。
这是我所做的：
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', 1Run 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]')
尝试更改 OLE 的配置，例如：
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运行查询：
SELECT * FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Text;Database=C:\Temp\;','SELECT * FROM [test.csv]')或者
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:
然后我检查了SQL server上的链接服务器，run system sp默认有10个provider：
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.0or MICROSOFT.JET.OLEDB.4.0is available for SQL Server?
如何解决这个问题？我如何知道SQL Server 是否可用MICROSOFT.ACE.OLEDB.12.0或MICROSOFT.JET.OLEDB.4.0是否可用？
采纳答案by Aleksandr Fedorenko
For file type with extention .xlsx use 'Excel 12.0' or 'Excel 12.0 Xml' instead of Excel 9.0
对于扩展名为 .xlsx 的文件类型，请使用“Excel 12.0”或“Excel 12.0 Xml”而不是 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:
如果要连接到 Microsoft Office Excel 数据，请根据 Excel 文件类型添加 OLEDB 连接字符串的适当扩展属性：
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"
