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

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

我正在尝试通过T-SQL查询将表数据导出到Excel中。经过一些研究，我想到了这个

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

Msg 7302，级别16，状态1，第7行不能为链接服务器创建一个OLEDB提供程序“Microsoft.Jet.OLEDB.4.0”的实例(null)。

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/

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

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

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

我在家里的电脑上做这个，我是管理员。仍然得到相同的错误。

SQL SERVER details

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

微软SQL Server 2016 (RC1) - 13.0.1200.242 (X64) 2016年3月10日16:49:45 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition(64位)on Windows 10 Pro 6.3 (Build 10586:)

Any pointers to fix the problem will be highly appreciated

任何解决问题的建议都将受到高度赞赏

Update : Already I have configured the Ad Hoc Distributed Queries and

更新:我已经配置了特殊的分布式查询和

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.

Msg 7438，级别16，状态1，第7行32位OLEDB提供程序“Microsoft.Jet.OLEDB.4.0”不能在64位SQL服务器上加载进程内。

4 个解决方案

#1

OK, I made it work for me, and it looks like SO will let me post today.

好吧，我让它为我工作，它看起来就像今天让我发布。

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。这似乎是非常挑剔的，所以您可能需要调整到您的特定版本。

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
下载Windows的Microsoft.ACE.OLEDB.12.0，这里有64位版本:https://www.microsoft.com/en-us/download/details.aspx?id=13255
Install it on your server.
在您的服务器上安装它。
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.
检查用户运行SQL Server并确保用户能够访问的临时目录C:\Windows\ ServiceProfiles当地\ temp \ LocalService \ AppData \如果是本地服务帐户或C:\Windows\ ServiceProfiles当地\ temp \ NetworkService \ AppData \如果它是一个网络服务帐户。
Configure 'Ad Hoc Distributed Queries' and enable the Microsoft.ACE.OLEDB files like this:
配置“临时分布式查询”并启用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

Make sure you register msexcl40.dll like this:

确保你注册了msexcl40。dll是这样的:

regsvr32 C:\Windows\SysWOW64\msexcl40.dll

#2

Check out sp_configure /RECONFIGURE...

看看sp_configure /配置……

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

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/

#3

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服务器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)下载Microsoft.ACE.OLEDB.12.0 for Windows，在这里找到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

#4

-1

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

#1