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

Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null

提问by P?????

I am trying to export from my Tabledata into Excelthrough T-SQLquery. After little research I came up with this

我正在尝试从我的Table数据导出到Excel通过T-SQL查询。经过一点研究，我想出了这个

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

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

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

消息 7302，级别 16，状态 1，第 7 行无法为链接服务器“（空）”创建 OLE DB 提供程序“Microsoft.Jet.OLEDB.4.0”的实例。

So went through internet for solution, got the below link

所以通过互联网寻求解决方案，得到了以下链接

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/

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

In the above link they were saying like we need to be administrator to create folder in C driveTEMPfolder since OPENROWSETcreates some files or folder inside TEMPfolder

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

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

我在我的家用 PC 中执行此操作，我是管理员。仍然得到同样的错误。

SQL SERVERdetails

SQL 服务器详细信息

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

Microsoft SQL Server 2016 (RC1) - 13.0.1200.242 (X64) 2016 年 3 月 10 日 16:49:45 版权所有 (c) Windows 10 Pro 6.3（内部版本 10586：）上的 Microsoft Corporation 企业评估版（64 位）

Any pointers to fix the problem will be highly appreciated

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

Update :Already I have configured the Ad Hoc Distributed Queriesand

更新：我已经配置了Ad Hoc Distributed Queries和

Executed the below 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 

now am getting this error

现在收到此错误

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.

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

回答by quest4truth

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.

我有 MS Sql server 2012 和 Office 2013。这看起来很挑剔，所以你可能需要适应你的特定版本。

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.OLEDBfiles like this:

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

Here's the SP_CONFIGURE commands:

这是 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

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

在较新的SQL Server 2014 上，您使用了'DynamicParameters'而不是'DynamicParam'

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

Make sure you register msexcl40.dll like this:

确保像这样注册 msexcl40.dll：

regsvr32 C:\Windows\SysWOW64\msexcl40.dll

回答by JimR

Check out sp_configure /RECONFIGURE...

查看 sp_configure /RECONFIGURE...

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

See these links for more info:

查看这些链接了解更多信息：

https://technet.microsoft.com/en-us/library/aa259616(v=sql.80).aspx

https://technet.microsoft.com/en-us/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/

http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-提供者用于在公寓模式中运行/

回答by FerroPont

Works !!! Great thanks. Just for 64-bit Win server 2012R2. Let me put the whole working script partially repeating bits from above which are not easy (as for me) to combine together:

作品！！！万分感谢。仅适用于 64 位 Win server 2012R2。让我把整个工作脚本部分重复上面的部分，这些部分不容易（对我而言）组合在一起：

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

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

2) Create excel file with respective columns (name and class in this case).

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

3) Run code below:

3）运行下面的代码：

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

/* -- Not sure commented bits are necessary but I also have run them
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 

-- below two lines failed with: Msg 2787, Level 16, State 1, Procedure sp_MSset_oledb_prop, Line 106 Invalid format specification: '%1!.'.
    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

-- but works all two as below:
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'DynamicParameters', 1
-- Succeded to run after success with Excel so does not looks like necessary


*/

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

回答by shrikrishna das

Please Execute the below queries to fix this problem:

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

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