SQL 链接服务器“（空）”的 OLE DB 提供程序“Microsoft.ACE.OLEDB.12.0”

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

提问by Steven Kanberg

I'm trying to run the following statement but am receiving the error messages just below. I have researched answers to no end and none have worked for me. I'm running Office 365 (64bit). I have loaded the Microsoft Access Database Engine (64bit). This is in Visual Studio 2013 with SSDT as well as SQL Server 2012. I do not have access to changing environment or startup parameters to SQL Server. Any help is appreciated.

我正在尝试运行以下语句，但收到下面的错误消息。我无休止地研究了答案，但没有一个对我有用。我正在运行 Office 365（64 位）。我已经加载了 Microsoft Access 数据库引擎（64 位）。这是在带有 SSDT 以及 SQL Server 2012 的 Visual Studio 2013 中。我无权更改 SQL Server 的环境或启动参数。任何帮助表示赞赏。

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0', 
    'Excel 12.0;Database=C:\Users\UserName\Folder\SomeFile.xlsx;;HDR=NO;IMEX=1', [Table 1$])

• Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
• Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)".

• 消息 7399，级别 16，状态 1，第 1 行链接服务器“(null)”的 OLE DB 提供程序“Microsoft.ACE.OLEDB.15.0”报告了错误。提供者没有提供有关错误的任何信息。
• 消息 7303，级别 16，状态 1，第 1 行无法初始化链接服务器“（空）”的 OLE DB 提供程序“Microsoft.ACE.OLEDB.15.0”的数据源对象。

Here's what I have tried:

这是我尝试过的：

First, I ran...

首先，我跑...

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

Followed by...with no love.

其次是……没有爱。

EXEC sys.sp_addsrvrolemember @loginame = N'<<Domain\User>>', @rolename = N'sysadmin';
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'DynamicParameters', 1 
GO 

I have changed the code to read Microsoft.ACE.OLEDB.12.0as I have seen that as well, still no love.

我已经更改了代码以读取Microsoft.ACE.OLEDB.12.0，正如我所看到的，仍然没有爱。

I have also checked permissions of C:\Users\MSSQLSERVER\AppData\Local\Temp and C:Windows\ServiceProfiles\NetworkService\AppData\Local which have granted Full Control for the following: System, MSSQLSERVER, and Administrators, Network Service (on the latter).

我还检查了 C:\Users\MSSQLSERVER\AppData\Local\Temp 和 C:Windows\ServiceProfiles\NetworkService\AppData\Local 的权限，它们已授予以下权限：系统、MSSQLSERVER 和管理员、网络服务（在后者）。

Still no love.

还是没有爱。

Lastly, I have tried changing to the 32bit version of the Microsoft Access Database Engine which has persisted in not working.

最后，我尝试更改为 32 位版本的 Microsoft Access 数据库引擎，该版本一直无法正常工作。

Help, anyone?

帮助，有人吗？

采纳答案by Rahul Goel

http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx

http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx

This solves the issue. For some reason SQL Server does not like the default MSSQLSERVER account. Switching it to a local user account resolves the issue.

这解决了这个问题。出于某种原因，SQL Server 不喜欢默认的 MSSQLSERVER 帐户。将其切换到本地用户帐户可解决此问题。

回答by The Red Pea

This is for my reference, as I encountered a variety of SQL error messages while trying to connect with provider. Other answers prescribe "try this, then this, then this". I appreciate the other answers, but I like to pair specific solutions with specific problems

这是供我参考，因为我在尝试与提供程序连接时遇到了各种 SQL 错误消息。其他答案规定“试试这个，然后这个，然后这个”。我很欣赏其他答案，但我喜欢将特定解决方案与特定问题配对

---

---

Error

错误

...provider did not give information...Cannot initialize data source object...

...提供者未提供信息...无法初始化数据源对象...

Error Numbers

错误编号

7399, 7303

7399、7303

Error Detail

错误详情

Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. 
  The provider did not give any information about the error. 
Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object
  of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

解决方案

File was open. Close it.

文件已打开。关闭它。

Credit

信用

• https://stackoverflow.com/a/29369868/1175496

• https://stackoverflow.com/a/29369868/1175496

---

---

Error

错误

Access denied...Cannot get the column information...

访问被拒绝...无法获取列信息...

Error Numbers

错误编号

7399, 7350

7399, 73 50

Error Detail

错误详情

Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. 
  Access denied.
Msg 7350, Level 16, State 2, Line 2 Cannot get the column information 
  from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

解决方案

Give access

授予访问权限

Credit

信用

• https://stackoverflow.com/a/27509955/1175496

• https://stackoverflow.com/a/27509955/1175496

---

---

Error

错误

No value given for one or more required parameters....Cannot execute the query ...

没有为一个或多个必需参数指定值....无法执行查询...

Error Numbers

错误编号

???, 7320

？？？, 73 20

Error Detail

错误详情

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 2
Cannot execute the query "select [Col A], [Col A] FROM　$Sheet" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". 

Solution

解决方案

Column names might be wrong. Do [Col A]and [Col B]actually exist in your spreadsheet?

列名可能有误。做[Col A]和[Col B]在电子表格中实际上存在吗？

---

---

Error

错误

"Unspecified error"...Cannot initialize data source object...

“未指定错误”...无法初始化数据源对象...

Error Numbers

错误编号

???, 7303

？？？, 7303

Error Detail

错误详情

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

解决方案

Run SSMS as admin. See this question.

以管理员身份运行 SSMS。看到这个问题。

---

---

Other References

其他参考

Other answers which suggest modifying properties. Not sure how modifying these two properties (checking them or unchecking them) would help.

其他建议修改属性的答案。不确定修改这两个属性（选中或取消选中它们）会有什么帮助。

• https://stackoverflow.com/a/31605038/1175496
• http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx
• https://social.technet.microsoft.com/Forums/lync/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access?forum=sqldataaccess#3fcc14f4-420e-4544-be74-eea1e0e78462

• https://stackoverflow.com/a/31605038/1175496
• http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx
• https://social.technet.microsoft.com/Forums/lync/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported- an-error-access?forum=sqldataaccess#3fcc14f4-420e-4544-be74-eea1e0e78462

回答by Michael M?ldrup

Instead of changing the user, I've found this advise:

我没有更改用户，而是找到了以下建议：

https://social.technet.microsoft.com/Forums/lync/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access?forum=sqldataaccess

https://social.technet.microsoft.com/Forums/lync/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported- an-error-access?forum=sqldataaccess

This might help someone else out - after trying every solution to trying and fix this error on SQL 64..

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

..I found an article here...

http://sqlserverpedia.com/blog/sql-server-bloggers/too-many-bits/

..which suggested I give Everyone full permission on this folder..

C:\Users\SQL Service account name\AppData\Local\Temp

And hey presto! My query suddenly burst into life. I punched the air in delight.

Edwaldo

这可能会帮助其他人 - 在尝试了所有解决方案以尝试并修复 SQL 64 上的此错误之后..

无法为链接服务器“(null)”初始化 OLE DB 访问接口“Microsoft.ACE.OLEDB.12.0”的数据源对象。

..我在这里找到了一篇文章...

http://sqlserverpedia.com/blog/sql-server-bloggers/too-many-bits/

..这建议我给予每个人对此文件夹的完全权限..

C:\Users\SQL 服务帐户名\AppData\Local\Temp

嘿，快！我的疑问突然迸发出来。我欣喜若狂。

埃德瓦尔多

回答by Drew

Make sure the Excel file isn't open.

确保 Excel 文件未打开。

回答by Diego Castro

In the SQL Server, try these steps:

在 SQL Server 中，尝试以下步骤：

1. Open one database.
2. Click in the option Server Object.
3. Click in Linked Servers.
4. Click in Providers.
5. Right click on Microsoft.ACE.OLEDB.12.0and click Properties.
6. Uncheck all the options and close.

1. 打开一个数据库。
2. 在选项中单击Server Object。
3. 点击进入Linked Servers。
4. 点击进入Providers。
5. 右键单击Microsoft.ACE.OLEDB.12.0并单击Properties。
6. 取消选中所有选项并关闭。

回答by MapLion

Here is specifically what worked for me only when the Excel file being queried was not openand when running the SQL Server Service as me[as a user that has access to the file system]. I see pieces of my answer already given elsewhere, so I apologize for any redundancy, but for the sake of a more succinct answer:

以下是仅当被查询的 Excel 文件未打开以及以我[作为有权访问文件系统的用户] 的身份运行 SQL Server 服务时才对我有用的方法。我看到我的部分答案已经在其他地方给出，所以我为任何冗余道歉，但为了更简洁的答案：

USE [master]
GO

EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO


SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0;Database=C:\MyExcelFile.xlsx',
                'SELECT * FROM [MyExcelSheetName$]')

回答by Mark Alberts

I'm running SQL Server 2014 64 biton Windows 10. I tried everything, the thing that made it work was:

我SQL Server 2014 64 bit在 Windows 10 上运行。我尝试了所有方法，使其工作的原因是：

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0

I don't know why the AllowInProcess turned off makes it work but that was the key in my case. Thank you for the suggestion of turning all the options off on the linkserver.

我不知道为什么 AllowInProcess 关闭使它起作用，但这是我的情况的关键。感谢您建议关闭链接服务器上的所有选项。

回答by wrightstork

I had the same issues even after running all those EXEC stored procedure commands that you see on every answer/blog, but when I ran SSMS as administrator, the problems went away. Since, I didn't want to do that, I opened Services, went to SQL Server (MyServer), right clicked, selected properties. On the Log On tab, "This account:" was selected with and listed my SQL Server account/password. I instead checked "Local System account" and ticked the "Allow service to interact with desktop". Then I stopped and started the service. Now, I can run the same query as the original poster mentions without running as administrator in SSMS. This probably worked because I am running SQL Server on my home desktop.

即使在运行您在每个答案/博客上看到的所有 EXEC 存储过程命令后，我也遇到了同样的问题，但是当我以管理员身份运行 SSMS 时，问题就消失了。因为，我不想这样做，所以我打开服务，转到 SQL Server (MyServer)，右键单击，选择属性。在“登录”选项卡上，选择了“此帐户：”并列出了我的 SQL Server 帐户/密码。我改为检查“本地系统帐户”并勾选“允许服务与桌面交互”。然后我停止并启动服务。现在，我可以运行与原始海报提到的相同的查询，而无需在 SSMS 中以管理员身份运行。这可能有效，因为我在我的家庭桌面上运行 SQL Server。

I'd also like to point out that you can use GUI in SSMS to enable adhoc access (and many other options). In SSMS, go to Linked Servers>Providers, right click on the provider, and select properties. Then you can just check/uncheck the ones you want.

我还想指出，您可以在 SSMS 中使用 GUI 来启用临时访问（以及许多其他选项）。在 SSMS 中，转到链接服务器>提供程序，右键单击提供程序，然后选择属性。然后你可以选中/取消选中你想要的。

回答by HamedH

Removing Read-only CheckBox from C:\Users\SQL Service account name\AppData\Local\Tempworked for me.

删除只读复选框C:\Users\SQL Service account name\AppData\Local\Temp对我有用。

回答by Vinit Bhardwaj

In my case this problem was occuring because i was accessing the file from shared folder by using computerName. OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='\ntpc0100\MysharedFolder\KPI_tempData\VariablePayoutDetails.xls';IMEX=1;'','.....) Insted of using computerName ntpc0100(whatever your machine name) you should specify IPaddress of your machine. for example:- OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0; Database='\193.34.23.200\MysharedFolder\KPI_tempData\KPIVariablePayoutDetails.xls'....);

就我而言，发生此问题是因为我使用 computerName 从共享文件夹访问文件。OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='\ntpc0100\MysharedFolder\KPI_tempData\VariablePayoutDetails.xls';IMEX=1;'','.....) Insted of使用 computerName ntpc0100（无论您的机器名称如何），您应该指定机器的 IP 地址。例如：- OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0; Database='\193.34.23.200\MysharedFolder\KPI_tempData\KPIVariablePayoutDetails.xls'....);