SQL Server 2012 Queries Access 2007 Data Using OPENROWSET Error

Question

I would like to request data in Management Studio from a Microsoft Access 2007 database located on the same computer as my instance of SQL Server 2012. I do not want to use a linked server to do this, because the user can select various Access databases. I follow the directions found on technet and in other sources that I read said to use OPENROWSET as the right way to do what I want, but when I do it in 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);

... I get the error message 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 administrator has permission to read this table. Access 2007 32bit is installed on a computer that is 64-bit and also an instance of SQL Server. I believe that SQL Server can access the database file because when I get 1 while doing this:

 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'm trying to accomplish?

Answer 1

Finally, after several unsuccessful attempts to get SQL Server to "talk" to the Access database - either as a Linked Server in SSMS or through OPENROWSET() in T-SQL - I found this blog post suggesting the following three (3) offers.

Tweak # 1: OLE DB Provider Settings

The OLE DB provider for ACE (or Jet) must have the Dynamic option and Allow inprocess options. In SSMS, open

Server Objects> Linked Servers> Providers

right-click "Microsoft.ACE.OLEDB.12.0" (or "Microsoft.Jet.OLEDB.4.0"), select "Properties" and make sure that these options are selected:

Tweak # 2: Temp folder permissions

This is what pounded me.

Apparently, SQL Server should write information to a temporary file when executing an OLE DB query against an Access database. Because SQL Server runs as a service, it uses the% TEMP% folder of the account under which the service runs.

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

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

and if it is running under the built-in Local Service account, then the temporary folder

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

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

Once I granted myself Modify permissions for this folder

and activate OPENROWSET requests as described 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 request worked fine:

Tweak # 3: memory_to_reserve

Although I did not need to use it in my case, the aforementioned blog also claims that setting the startup option “-g memory_to_reserve” to the SQL Server service can also help avoid such errors. For this:

• start SQL Server Configuration Manager
• right-click the SQL Server service (SQL Server Services tab) and select Properties
• on the Advanced tab, add -g512; in the "Launch Settings" setting
• restart the SQL Server service

For more information on the "memory_to_reserve" parameter, see the MSDN article here .

Answer 2

This should work

---

---- EXEC sp_configure 'show additional parameters', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure "Special Distributed Requests", 1;
GO
RECONFIGURE;
GO

USE [database_name]

GO SET ANSI_NULLS ON GO GO SET QUOTED_IDENTIFIER ON GO

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

Answer 3

Assuming that the Northwind sample is installed (and in the folder mentioned below), will the next run be performed?

 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 the last reimage