OLE DB提供者"Microsoft.ACE.OLEDB.12.0&"被提供.用于链接服务器“(null)" [英] The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

问题描述

我正在尝试运行以下语句，但是正在接收下面的错误消息.我研究了无穷无尽的答案，但没有一个对我有用.我正在运行Office 365(64位).我已经加载了Microsoft Access数据库引擎(64位).这在带有SSDT的Visual Studio 2013和SQL Server 2012中.我无法访问更改环境或SQL Server的启动参数.感谢您的帮助.

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.

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

• 消息7399，级别16，状态1，第1行OLE DB提供程序 链接服务器(null)"的"Microsoft.ACE.OLEDB.15.0"报告了一个 错误.提供者未提供有关该错误的任何信息.
• 消息7303，级别16，状态1，行1无法初始化数据源 链接的OLE DB提供程序"Microsoft.ACE.OLEDB.15.0"的对象 服务器(空)".
• 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)".

这是我尝试过的:

首先，我跑了...

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

其次是……没有爱.

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 

我已经将代码更改为读取 Microsoft.ACE.OLEDB.12.0 ，因为我也看到了，但还是没有爱.

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

我还检查了C:\ Users \ MSSQLSERVER \ AppData \ Local \ Temp和C:Windows \ ServiceProfiles \ NetworkService \ AppData \ Local的权限，这些权限已授予以下人员的完全控制权:系统，MSSQLSERVER和管理员，网络服务(在后者上).

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

仍然没有爱.

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

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

有帮助吗?

推荐答案

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

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.

这篇关于OLE DB提供者"Microsoft.ACE.OLEDB.12.0&"被提供.用于链接服务器“(null)"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！