如何查找哪个OLE提供程序可用于SQL Server? [英] How to find which OLE provider is available for SQL Server?
本文介绍了如何查找哪个OLE提供程序可用于SQL Server?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我尝试访问SSMS中的Excel文件。上网搜了一遍,也没能用上。
以下是我所做的:
我的环境:
Windows 7(64bit) SP 1,
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
Office 2010 Pro Plus with Access installed(32 bit)
尝试更改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]')
对于这两种情况,我都收到如下错误消息:
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.
或
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.
然后我检查了SQL SERVER上的链接服务器,默认情况下Run System SP有10个提供程序:
EXEC master.dbo.sp_MSset_oledb_prop
SQLOLEDB
MSOLAP
SQLNCLI11
ADsDSOObject
SQLNCLI
SQLNCLI10
Search.CollatorDSO
MSDASQL
MSDAOSP
MSIDXS
如何解决这个问题?
如何知道MICROSOFT.ACE.OLEDB.12.0
或MICROSOFT.JET.OLEDB.4.0
是否可用于SQL Server?
推荐答案
对于扩展名为.xlsx的文件类型,使用‘Excel12.0’或‘Excel12.0XML’而不是Excel9.0
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:TempTest.xlsx;', 'SELECT * FROM [Location1$]')
如果要连接到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"
这篇关于如何查找哪个OLE提供程序可用于SQL Server?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文