SQL Server 2012 使用 OPENROWSET 错误查询 Access 2007 数据

我想从与 SQL Server 2012 实例位于同一台计算机上的 Microsoft Access 2007 数据库查询 Management Studio 中的数据.我不想使用链接服务器来执行此操作，因为用户可以选择不同的 Access 数据库.我正在遵循 中记录启用了 OPENROWSET 查询，即 ...

EXEC sp_configure '显示高级选项', 1重新配置去EXEC sp_configure 'ad hoc 分布式查询', 1重新配置去

...我的查询工作正常:

调整 #3:memory_to_reserve

虽然在我的情况下不需要使用它，但前面提到的博客也声称调整 SQL Server 服务的-g memory_to_reserve"启动参数也有助于避免类似错误.要做到这一点:

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

有关memory_to_reserve"设置的更多详细信息，请参阅 MSDN 文章此处.

I would like to query data in Management Studio from a Microsoft Access 2007 database located on the same machine as my SQL Server 2012 instance. I do NOT want to use a linked server to do this as different Access databases can be chosen by the user. I am following the directions found on technet and other sources I have read said to use OPENROWSET as the proper way to do what I want, but when I execute this in Management Studio...

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:Usersoliver.klosoffDesktopNew folder41000-13-0085 Consolidated Killers LLC.mdb'; 'admin';'',tblTtlHrsFringes);

...I get the error below:

Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

The database does not have a password set for the admin user, and the admin user has permissions to read this table. Access 2007 32bit is installed on the machine, which is 64 bit, as well as the SQL Server instance. I believe that SQL Server can access the database file because when I get 1 when I execute this:

DECLARE @out INT
EXEC master.dbo.xp_fileexist 'C:Usersoliver.klosoffDesktopNew folder41000-13-0085    Consolidated Killers LLC.mdb', @out OUTPUT
SELECT @out`

Is there a way to do what I am trying to accomplish?

解决方案

Finally, after several unsuccessful attempts to have SQL Server "talk to" an Access database – either as a "Linked Server" in SSMS or via OPENROWSET() in T-SQL – I found this blog post that offered the following three (3) suggestions.

Tweak #1: OLE DB Provider settings

The OLE DB Provider for ACE (or Jet) must have the "Dynamic parameter" and "Allow inprocess" options enabled. In SSMS, open the

Server Objects > Linked Servers >Providers

branch, right-click "Microsoft.ACE.OLEDB.12.0" (or "Microsoft.Jet.OLEDB.4.0"), choose "Properties", and ensure that those options are selected:

Tweak #2: Temp folder permissions

This is the one that was stumping me.

Apparently SQL Server needs to write information into a temporary file while running an OLE DB query against an Access database. Because SQL Server is running as a service it uses the %TEMP% folder of the account under which the service is running.

If the SQL Server service is running under the built-in "Network Service" account then the temp folder is

%SystemRoot%ServiceProfilesNetworkServiceAppDataLocalTemp

and if it is running under the built-in "Local Service" account then the temp folder is

%SystemRoot%ServiceProfilesLocalServiceAppDataLocalTemp

My problem was that SSMS was running under my account (not NETWORK SERVICE) so I only had Read access to the Temp folder

Once I granted myself Modify permissions on that folder

and enabled OPENROWSET queries as documented in another question here, namely ...

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

... my query worked fine:

Tweak #3: memory_to_reserve

Although I didn't need to use it in my case, the aforementioned blog also claims that adjusting the "-g memory_to_reserve" startup parameter for the SQL Server service can also help avoid similar errors. To do that:

• launch SQL Server Configuration Manager
• right-click the SQL Server service ("SQL Server Services" tab) and choose "Properties"
• on the "Advanced" tab, prepend -g512; to the "Startup Parameters" setting
• restart the SQL Server service

For more details on the "memory_to_reserve" setting see the MSDN article here.