无法初始化OLE DB提供程序"Microsoft.ACE.OLEDB.12.0"的数据源对象.用于链接服务器 [英] Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server

问题描述

我正在尝试建立从SQL Server 2008 R2到Access database的链接服务器. Access database没有密码保护.正如我在其他帖子中看到的那样(中的每个临时文件夹，但无济于事.

I'm trying to establish a linked server from SQL Server 2008 R2 to an Access database. The Access database is not password protected. As I have seen in other posts (specifically this one), this is usually due to the current user not having access to the Temp folder inside the NetworkService folder. Since we are running this in Windows Server 2008, the directory structure is quite different than what most are eluding to. I have allowed access to every temp folder in the "root:\Documents and Settings\" directory, and to no avail.

这是我用来添加链接服务器的过程:

This is the procedure I am using to add the linked server:

EXEC sp_addlinkedserver 
@server = N'OS_Access', 
@provider = N'Microsoft.ACE.OLEDB.12.0', 
@srvproduct = N'',
@datasrc = N'C:\RTBData\Data\OS.mdb';
GO

它可以毫无问题地创建链接服务器，但是我无法查看数据库的表/视图.同样，我的ASP.NET应用程序也无法访问它.

It creates the linked server with no problem, but I am not able to view the tables/views of the database. Likewise, my ASP.NET application cannot access it either.

我已经尝试了两个ACE and JET(64-bit)(通过安装Office的数据连接组件)，但是它们都无法正常工作.我还尝试将"Dynamic Parameter"和"Allow InProcess"的ACE提供程序配置为true.

I have tried both ACE and JET(64-bit)(by installing the Data Connectivity Components for Office), and they both do not work. I have also tried configuring the ACE provider with "Dynamic Parameter" and "Allow InProcess" to true.

此外，我尝试通过在SQL配置管理器的"startup parameters"字符串前面添加"-g512;来增加MSSQLSERVER services的内存使用量，以排除内存问题.

Additionally, I tried upping the memory usage by the MSSQLSERVER services by adding "-g512;" in front of "startup parameters" string in SQL configuration manager to rule out memory issues.

如果有人可以阐明这一点，那就太好了！谢谢！

If anyone could shed some light on this that would be fantastic! Thanks!

更新:我使用sa帐户登录到SQL，并且能够查看链接的服务器.我当前的用户帐户在SQL Server中具有完全权限，因此我不确定这些帐户之间有什么区别.也许我将权限授予错误的Temp文件夹，其中sa帐户(内置)可以访问该文件夹.如果有人可以在这里向我指出正确的方向，那就太好了，而且我认为这也会对很多其他人有所帮助.

UPDATE: I logged into SQL using the sa account, and I was able to view the linked server. My current user account has full permissions in SQL server, so I am unsure of what the difference is between the accounts. Maybe I gave permissions to the wrong Temp folder, in which the sa account (being built-in) has access to. If someone could point me in the right direction here, that would be great, and I think it would also help a lot of other people.

推荐答案

我通过在控制面板中禁用UAC解决了该问题.我不确定这会带来什么变化，但这可能会对其他人有所帮助！

I fixed the problem by disabling UAC within the control panel. I am unsure what changes this made, but it might help others!

这篇关于无法初始化OLE DB提供程序"Microsoft.ACE.OLEDB.12.0"的数据源对象.用于链接服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！