OLE DB提供程序“Microsoft.Jet.OLEDB.4.0”不能用于分布式查询 [英] OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries

问题描述

我想使用查询将数据从Excel导入SQL Server，而不是使用向导。我试过这个查询：

I want to import data from Excel to SQL Server using queries, not by using a wizard. I tried this query:

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:

消息7308，级别16，状态1，行1号
OLE DB提供程序Microsoft.Jet.OLEDB.4.0不能用于分布式查询，因为提供程序是配置为以单线程公寓模式运行。

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.

所以我在Google上搜索，我得到了如下答案：

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...

推荐答案

根据这个线程，

Microsoft.Jet.OLEDB.4.0不支持64位操作系统

Microsoft.Jet.OLEDB.4.0 is not supported for 64-bit OS

假设您正在运行SQL Server 64位，可能需要使用 64位 Microsoft Access数据库引擎2010可再发行组织 。

Assuming you are running SQL Server 64-bit, you likely need the 64-bit Microsoft Access Database Engine 2010 Redistributable.

请注意，如果已安装其他版本，尝试安装软件时会有轻微的皱纹。在这种情况下，使用 / passive 开关从命令行安装第二个版本。根据这个线程：

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 /passive switch. According to this thread:

启动安装Microsoft ACE具有Office安装（不包括当前安装）的机器上的OLEDB提供程序将导致安装失败。要正常运行，您需要从指定的/ 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安装，但同样适用于共存的数据库引擎安装。

That is talking about an existing Office install but the same applies to coexisting database engine installations.

编辑：还要确保使用Microsoft.ACE.OLEDB。 12.0不是Microsoft.Jet.OLEDB.4.0的提供程序字符串。 （Props to @Rumi）

Also make sure to use "Microsoft.ACE.OLEDB.12.0" not "Microsoft.Jet.OLEDB.4.0" for the provider string. (Props to @Rumi)

这篇关于OLE DB提供程序“Microsoft.Jet.OLEDB.4.0”不能用于分布式查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！