SQL OLE DB 提供程序“Microsoft.Jet.OLEDB.4.0”不能用于分布式查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22032222/
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
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries
提问by P?????
I want to import data from Excel to SQL Server using queries, not by using a wizard. I tried this query:
我想使用查询将数据从 Excel 导入 SQL Server，而不是使用向导。我试过这个查询：
Select * INTO g FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 12.0;Database=D:\new.xlsx;HDR=YES', 'SELECT * FROM [newSheet$]');
But, I am getting this error:
但是，我收到此错误：
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.
消息 7308，级别 16，状态 1，第 1 行
OLE DB 提供程序“Microsoft.Jet.OLEDB.4.0”不能用于分布式查询，因为该提供程序配置为在单线程单元模式下运行。
So I searched on Google, and I got answers like:
所以我在谷歌上搜索，我得到了类似的答案：
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Even after reconfiguring it is showing me the same error...
即使在重新配置后，它也向我显示了同样的错误......
回答by agentnega
According to this thread,:
根据这个线程，：
Microsoft.Jet.OLEDB.4.0 is not supported for 64-bit OS
64 位操作系统不支持 Microsoft.Jet.OLEDB.4.0
Assuming you are running SQL Server 64-bit, you likely need the 64-bit Microsoft Access Database Engine 2010 Redistributable.
假设您运行的是 64 位 SQL Server，您可能需要64 位Microsoft Access Database Engine 2010 Redistributable。
And be aware that there is a minor wrinkle when trying to install the software if the other version is already installed. In this case install the second version from the command line using the /passiveswitch. According to this thread:
请注意，如果已经安装了其他版本，则在尝试安装该软件时会出现小问题。在这种情况下，使用/passive开关从命令行安装第二个版本。根据这个线程：
Launching the install of a Microsoft ACE OLEDB Provider on a machine with an Office install other than the current one (e.g. 32 on 64) will cause the install to fail. To have it run properly you need to launch it from a command line with the “/passive”argument specified.
在安装了不同于当前安装的 Office（例如 64 上的 32）的计算机上启动 Microsoft ACE OLEDB 提供程序的安装将导致安装失败。要使其正常运行，您需要使用指定的“/passive”参数从命令行启动它。
That is talking about an existing Office install but the same applies to coexisting database engine installations.
这是在谈论现有的 Office 安装，但同样适用于共存的数据库引擎安装。
EDIT: Also make sure to use "Microsoft.ACE.OLEDB.12.0" not "Microsoft.Jet.OLEDB.4.0" for the provider string. (Props to @Rumi)
编辑：还要确保对提供程序字符串使用“Microsoft.ACE.OLEDB.12.0”而不是“Microsoft.Jet.OLEDB.4.0”。（@Rumi 的道具）
回答by Nitu Bansal
Use SQL like below:
使用如下 SQL：
SELECT * into temptable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 12.0;Database=D:\new.xlsx','select * from [sheet1$]')
