OLE DB 提供程序“Microsoft.ACE.OLEDB.12.0"对于链接服务器“(空)"； [英] 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:UsersUserNameFolderSomeFile.xlsx;;HDR=NO;IMEX=1', [Table 1$])

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

这是我尝试过的:

首先，我跑...

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

跟着...没有爱.

EXEC sys.sp_addsrvrolemember @loginame = N'<<DomainUser>>', @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:UsersMSSQLSERVERAppDataLocalTemp 和 C:WindowsServiceProfilesNetworkServiceAppDataLocal 的权限，它们已授予以下权限:系统、MSSQLSERVER 和管理员、网络服务(后者).

I have also checked permissions of C:UsersMSSQLSERVERAppDataLocalTemp and C:WindowsServiceProfilesNetworkServiceAppDataLocal 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.

有人帮忙吗?

推荐答案

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

这解决了问题.出于某种原因，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"对于链接服务器“(空)"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！