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

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

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

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Users\oliver.klosoff\Desktop\New folder\41000-13-0085 Consolidated Killers LLC.mdb'; 'admin';'',tblTtlHrsFringes);

SELECT * FROM OPENROWSET（'Microsoft.Jet.OLEDB.4.0'，'C：\ Users \ oliver.klosoff \ Desktop \ New folder \ 41000-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)".

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

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:

数据库没有为admin用户设置密码，admin用户有权读取此表。 Access 2007 32bit安装在64位的计算机上，以及SQL Server实例。我相信SQL Server可以访问数据库文件，因为当我执行此操作时获得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`

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

有没有办法做我想要完成的事情？

4 个解决方案

#1

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.

最后，在几次尝试让SQL Server“与”Access数据库“对话” - 在SSMS中作为“链接服务器”或通过T-SQL中的OPENROWSET（）失败后，我发现这个博客文章提供了以下三个（3）建议。

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

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

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:

分支，右键单击“Microsoft.ACE.OLEDB.12.0”（或“Microsoft.Jet.OLEDB.4.0”），选择“属性”，并确保选中这些选项：

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.

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

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

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

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

％SYSTEMROOT％\ ServiceProfiles \网络服务\应用程序数据\本地的\ Temp

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

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

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

％SYSTEMROOT％\ ServiceProfiles \本地服务\应用程序数据\本地的\ Temp

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

我的问题是SSMS在我的帐户下运行（不是NETWORK SERVICE）所以我只对Temp文件夹有Read访问权限

Once I granted myself Modify permissions on that folder

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

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

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

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:

虽然我不需要在我的情况下使用它，但前面提到的博客还声称调整SQL Server服务的“-g memory_to_reserve”启动参数也可以帮助避免类似的错误。要做到这一点：

• launch SQL Server Configuration Manager
• 启动SQL Server配置管理器
• right-click the SQL Server service ("SQL Server Services" tab) and choose "Properties"
• 右键单击SQL Server服务（“SQL Server服务”选项卡），然后选择“属性”
• on the "Advanced" tab, prepend -g512; to the "Startup Parameters" setting
• 在“高级”选项卡上，前缀-g512;到“启动参数”设置
• restart the SQL Server service
• 重新启动SQL Server服务

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

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

#2

This should work

这应该工作

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

USE [DatabaseName]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SELECT *  FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source="C:\Employees.accdb"')...tblEMPS;

#3

Assuming you have the 'Northwind' sample installed (and in folder mentioned below), will the following run?

假设您安装了“Northwind”示例（并在下面提到的文件夹中），以下运行？

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
      'admin';'',Customers);
GO

Unfortunately I have not reinstalled SQL Server since my last reimage

不幸的是，自从我上次重新映像以来，我还没有重新安装SQL Server

#4

Microsoft.Jet.OLEDB.4.0 may not work now(in 2018). Need to download AccessDatabaseEngine_X64.exe or AccessDatabaseEngine.exe to SQL server and intall it. Then use Microsoft.ACE.OLEDB.12.0.

Microsoft.Jet.OLEDB.4.0现在可能无法正常工作（2018年）。需要将AccessDatabaseEngine_X64.exe或AccessDatabaseEngine.exe下载到SQL服务器并安装它。然后使用Microsoft.ACE.OLEDB.12.0。