无法为连接的服务器null创建OLE DB提供程序Microsoft.Jet.OLEDB.4.0的实例。

Question

我正在尝试通过T-SQL查询将我的Table数据导出到Excel。经过一番研究，我得到了以下代码：

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 
                        'Excel 8.0;Database=G:\Test.xls;', 
                        'SELECT * FROM [Sheet1$]') 
SELECT * 
FROM   dbo.products 

当我执行上述查询时，出现了以下错误：

Msg 7302, Level 16, State 1, Line 7 Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

所以我在互联网上寻找解决方案，得到了下面的链接：

https://blogs.msdn.microsoft.com/spike/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error/

在上面的链接中，他们说我们需要成为管理员才能在C驱动器 TEMP 文件夹中创建文件夹，因为 OPENROWSET 在 TEMP 文件夹中创建一些文件或文件夹。

我正在我的家用电脑上进行此操作，我是管理员。仍然遇到相同的错误。

SQL SERVER 详细信息

Microsoft SQL Server 2016 (RC1) - 13.0.1200.242 (X64) Mar 10 2016 16:49:45 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 10 Pro 6.3 (Build 10586: )

任何解决问题的指针将不胜感激

更新: 我已经配置了 Ad Hoc Distributed Queries 并执行了以下查询

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1
GO 

现在我遇到了这个错误

消息 7438，级别 16，状态 1，行 7 Microsoft.Jet.OLEDB.4.0 32位OLE DB提供程序无法在64位SQL Server中进程内加载。

- Pரதீப்

---

可能是 SQL 进程和 OLEDB 驱动程序之间的 64/32 位问题？ - Simon Mourier

SQL Server和MS Office（或仅使用OLEDB驱动程序）必须是64位版本。更多信息请参见此处http://dba.stackexchange.com/questions/60094/sql-server-error-importing-an-excel-sheet。 - gofr1

@Alex - 如何检查 SQL Server 服务是否在管理员帐户下运行？目前我正在使用 Windows 登录来登录 SQL Server。 - Pரதீப்

@Prdp - "开始 -> 所有程序 -> Microsoft SQL Server xxxx -> SQL Server xxxx 配置管理器" 或者打开 "服务控制管理器" (管理工具 -> 服务)。在列表中找到 "SQL Server"，然后右键单击并选择属性，然后选择 "登录" 选项卡。检查它正在运行的帐户。您也可以在那里更改帐户。 - Alex

你能否安装ACE驱动程序而不是Jet驱动程序，并使用OPENROWSET（'Microsoft.ACE.OLEDB.12.0'）？ - sqlab

Answer 1

我有 MS Sql Server 2012 和 Office 2013。这似乎非常挑剔，因此您可能需要根据您的特定版本进行调整。

1. 从此处下载 Windows 的 Microsoft.ACE.OLEDB.12.0，64位版本：https://www.microsoft.com/en-us/download/details.aspx?id=13255
2. 在服务器上安装它。
3. 检查运行 SQL Server 的用户，并确保该用户可以访问 temp 目录（如果是本地服务帐户，则为 C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp；如果是网络服务帐户，则为 C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp）。
4. 像这样配置“Ad Hoc Distributed Queries”并启用 Microsoft.ACE.OLEDB 文件：

以下是 SP_CONFIGURE 命令：

SP_CONFIGURE 'show advanced options', 1; 
GO 
RECONFIGURE; 
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; 
GO 
RECONFIGURE; 
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1   
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParam', 1

在较新的SQL Server 2014版本中，您需要使用'DynamicParameters'而不是'DynamicParam'

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

请确保您按照以下方式注册msexcl40.dll：

regsvr32 C:\Windows\SysWOW64\msexcl40.dll

- quest4truth

---

请确保您按照以下方式注册msexcl40.dll：regsvr32 C:\Windows\SysWOW64\msexcl40.dll - quest4truth

基本思路是创建您的关联服务器，并确保您的Excel格式与查询匹配。但是，SO（指Stack Overflow）没有让我放入正确的语法。我会在另一台电脑上尝试这个。 - quest4truth

好的，这是剩下的部分。在另一台电脑上，希望这会有所不同。像这样添加您的链接服务器：EXEC master.dbo.sp_addlinkedserver @server=N'任意服务器名称', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\Path\to\your\excel\file.xlsx', @provstr=N'Excel 12.0, HDR=YES' - quest4truth

现在，这两个查询都可以使用：选择查询： SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Test/x;sx'JDR=YES;IMEX=1', SELECT * FROM [Sheet1$]')插入查询: INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Test/x;sx'JDR=YES;IMEX=1', SELECT * FROM [Sheet1$]') SELECT * FROM YourTable - quest4truth

在 SQL Server 2014 中，我必须执行 'EXEC sp_configure' 并使用 'DynamicParameters' 替换 'DynamicParam'。 - mrówa

Answer 2

查看 sp_configure /RECONFIGURE...

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

请参阅以下链接获取更多信息： https://technet.microsoft.com/zh-cn/library/aa259616(v=sql.80).aspx http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-provider-is-used-to-run-in-apartment-mode/

- JimR

Answer 3

成功了！非常感谢。

仅适用于64位Win Server 2012R2。以下是完整的工作脚本，部分重复了上面的内容，这些内容不容易（对我来说）组合在一起：

1. 从此处下载适用于Windows的Microsoft.ACE.OLEDB.12.0，64位版本：https://www.microsoft.com/en-us/download/details.aspx?id=13255

2. 创建具有相应列的Excel文件（在本例中为名称和类别）。

3. 运行下面的代码：

sp_configure 'show advanced options'，1;  
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries'，1;  
RECONFIGURE;  
GO
-- 直到SQL Server 2012
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'，N'AllowInProcess'，1   
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0'，N'AllowInProcess'，1
-- SQL Server 2014或更高版本
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0'，N'DynamicParameters'，1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'，N'DynamicParameters'，1

-- 现在你可以导出到Excel
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0'， 
'Excel 8.0;Database=C:\testing.xlsx;'， 
'SELECT Name, Class FROM [Sheet1$]') 
SELECT [Name]，[Class] FROM Qry_2
GO
-- 或从Excel导入
select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0'， 
'Excel 8.0;Database=c:\targetWorkbook.xls;'， 
'SELECT * FROM [targetSheet$]') 

- FerroPont

Answer 4

请执行以下查询以解决此问题：

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1
GO 

- shrikrishna das