sql-server - SQL Server 2012 使用 OPENROWSET 查询 Access 2007 数据时出错

Question

我想从与 SQL Server 2012 实例位于同一计算机上的 Microsoft Access 2007 数据库查询 Management Studio 中的数据。我不想使用链接服务器来执行此操作，因为用户可以选择不同的 Access 数据库。我按照 technet 上找到的说明进行操作我读过的其他来源说使用 OPENROWSET 作为执行我想要的操作的正确方法，但是当我在 Management Studio 中执行此操作时...

选择*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Users\oliver.klosoff\Desktop\New 文件夹\41000-13-0085 Consolidated Killers LLC.mdb';
'管理员';'',tblTtlHrsFringes);

...我收到以下错误:

消息 7302，级别 16，状态 1，第 1 行
无法为链接服务器“(null)”创建 OLE DB 提供程序“Microsoft.Jet.OLEDB.4.0”的实例。

数据库没有为admin用户设置密码，而admin用户有读取该表的权限。在64位的机器上安装了Access 2007 32位，以及SQL Server实例。我相信 SQL Server 可以 Access 数据库文件，因为当我执行此命令时得到 1:

DECLARE @out INT
EXEC master.dbo.xp_fileexist 'C:\Users\oliver.klosoff\Desktop\New folder\41000-13-0085    Consolidated Killers LLC.mdb', @out OUTPUT
SELECT @out`

有办法实现我想要完成的任务吗？

Answer 1

最后，在多次尝试让 SQL Server 与 Access 数据库“对话”(无论是作为 SSMS 中的“链接服务器”还是通过 T-SQL 中的 OPENROWSET())失败之后，我发现了 this blog post提供了以下三 (3) 条建议。

调整 #1:OLE DB 提供程序设置

ACE(或 Jet)的 OLE DB 提供程序必须启用“动态参数”和“允许进程内”选项。在 SSMS 中，打开

服务器对象 > 链接服务器 > 提供程序

分支，右键单击“Microsoft.ACE.OLEDB.12.0”(或“Microsoft.Jet.OLEDB.4.0”)，选择“属性”，并确保选择这些选项:

调整 #2:临时文件夹权限

这就是困扰我的问题。

显然，当对 Access 数据库运行 OLE DB 查询时，SQL Server 需要将信息写入临时文件。由于 SQL Server 作为服务运行，因此它使用运行该服务的帐户的 %TEMP% 文件夹。

如果 SQL Server 服务在内置“网络服务”帐户下运行，则临时文件夹为

%SystemRoot%\ServiceProfiles\NetworkService\AppData\Local\Temp

如果它在内置的“本地服务”帐户下运行，则临时文件夹为

%SystemRoot%\ServiceProfiles\LocalService\AppData\Local\Temp

我的问题是 SSMS 在我的帐户(而不是网络服务)下运行，因此我只有对 Temp 文件夹的读取 Access 权限

一旦我授予自己对该文件夹的修改权限

并启用 OPENROWSET 查询，如另一个问题 here 中所述，即...

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

...我的查询运行良好:

调整 #3:memory_to_reserve

虽然我在我的例子中不需要使用它，但上述博客还声称调整 SQL Server 服务的“-g memory_to_reserve”启动参数也可以帮助避免类似的错误。为此:

• 启动 SQL Server 配置管理器
• 右键单击 SQL Server 服务(“SQL Server 服务”选项卡)并选择“属性”
• 在“高级”选项卡上，将 -g512; 添加到“启动参数”设置
• 重新启动 SQL Server 服务

有关“memory_to_reserve”设置的更多详细信息，请参阅 MSDN 文章 here。