无法为链接的服务器创建OLE DB提供程序Microsoft.Jet.OLEDB.4.0的实例 [英] Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null

问题描述

我正在尝试通过T-SQL查询将我的Table数据导出到Excel中.经过很少的研究，我想到了

I am trying to export from my Table data into Excel through T-SQL query. After little research I came up with this

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

当我执行上述查询时出现此错误

When I execute the above query am getting this error

消息7302，级别16，状态1，行7无法创建OLE的实例 链接服务器(null)"的数据库提供程序"Microsoft.Jet.OLEDB.4.0".

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

因此通过互联网寻求解决方案，获得了以下链接

So went through internet for solution, got the below link

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

In the above link they were saying like we need to be administrator to create folder in C drive TEMP folder since OPENROWSET creates some files or folder inside TEMP folder

我正在我的家用PC上执行此操作，我是管理员.仍然出现相同的错误.

I am doing this in My Home PC and I am the administrator. Still am getting the same error.

SQL SERVER 详细信息

Microsoft SQL Server 2016(RC1)-13.0.1200.242(X64)2016年3月10日 16:49:45版权所有(c)微软公司企业评估 Windows 10 Pro 6.3(Build 10586:)上的Edition(64位)

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: )

任何解决此问题的指针将受到高度赞赏

Any pointers to fix the problem will be highly appreciated

更新:我已经配置了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行32位OLE DB提供程序 无法在64位SQL上在进程中加载​​"Microsoft.Jet.OLEDB.4.0" 服务器.

Msg 7438, Level 16, State 1, Line 7 The 32-bit OLE DB provider "Microsoft.Jet.OLEDB.4.0" cannot be loaded in-process on a 64-bit SQL Server.

推荐答案

我有MS Sql Server 2012和Office2013.这似乎非常挑剔，因此您可能必须调整为特定的版本.

I have MS Sql server 2012, and Office 2013. This seems to be very finicky, so you may have to adjust to your particular versions.

1. 下载适用于Windows的Microsoft.ACE.OLEDB.12.0(64位版本)，可在此处找到: https://www.microsoft.com/zh-cn/download/details.aspx?id=13255
2. 将其安装在服务器上.
3. 检查运行SQL Server的用户，并确保该用户有权访问临时目录C:\ Windows \ ServiceProfiles \ LocalService \ AppData \ Local \ Temp(如果它是本地服务帐户)或C:\ Windows \ ServiceProfiles \ NetworkService \ AppData \ Local \ Temp(如果是网络服务帐户).
4. 配置临时分布式查询"并启用Microsoft.ACE.OLEDB文件，如下所示:

1. Download the Microsoft.ACE.OLEDB.12.0 for Windows, 64 bit version found here: https://www.microsoft.com/en-us/download/details.aspx?id=13255
2. Install it on your server.
3. Check the user running SQL Server and make sure that user has access to the temp directory C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp if it's a local service account or C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp if it's a network service account.
4. Configure 'Ad Hoc Distributed Queries' and enable the Microsoft.ACE.OLEDB files like this:

这是SP_CONFIGURE命令:

Here's the SP_CONFIGURE commands:

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'

On newer SQL Server 2014 You had use 'DynamicParameters' instead of 'DynamicParam'

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

确保像这样注册msexcl40.dll:

Make sure you register msexcl40.dll like this:

regsvr32 C:\Windows\SysWOW64\msexcl40.dll

这篇关于无法为链接的服务器创建OLE DB提供程序Microsoft.Jet.OLEDB.4.0的实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！