Home Dashboard Directory Help
Search

Connecting via a linked server to an access 2010 database file by TechVsLife2


Status: 

Active


22
0
Sign in
to vote
Type: Bug
ID: 587897
Opened: 8/20/2010 3:36:35 PM
Access Restriction: Public
5
Workaround(s)
view
11
User(s) can reproduce this bug

Description

TO CLARIFY: The linked server feature in x64 versions of sql server is NOT working against access/ace/accdb files using ANY driver (ACE 64-or 32-bit).

NOTE: The issue has been confirmed with Jin Chen at Microsoft.
This is on windows 7 x64. I keep getting an error when setting up a linked server in sql server 2008 R2 x64 to a microsoft access database file (mdb or accdb file). I have Access 2010 x64 installed (as part of ms office 2010 x64). (The exact same failure is also caused with 32 bit access 2010 installed, on a win7 x64 system.)

This is after right clicking linked servers in ssms and selecting "add new linked server," or using the sp procedure in "steps to reproduce" below.
These settings do NOT work (they are supposed to):
Provider (from dropdown): Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product name: Access
Data source: C:\foopath\foo.mdb --also fails with foo.accdb
Provider string: Microsoft.ACE.OLEDB.12.0

The error is:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER" returned message "Could not find installable ISAM.". (Microsoft SQL Server, Error: 7303)

I tried the jet 4.0 connection string and got this error when following the directions in Books Online (under sp_addlinkedserver):
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)

I simply don't have any connection string that works for adding a linked server to access 2010. Nothing in BOL or on the support threads is working for me.
Thank you.

Additional Note:
I can get linked servers to work against *Excel* files, but something seems completely broken with *access* database files (no linked server syntax that I can come up with works against access database files). The working syntax for excel spreadsheet files is as follows: (--using ad hoc distributed query, which can be converted to linked server using the sp in Steps To Reproduce):

SELECT * FROM OPENROWSET
('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=c:\foopath\foo.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [fooworksheetname$]');

To summarize, the issue can be reproduced in three ways:
1. via the ssms ui for adding a linked server (right-clicking on linked servers in ssms explorer window and selecting add linked server).
2. via the system sproc (sp_addlinkedserver), see below "steps to reproduce".
3. via the SELECT * FROM OPENROWSET method, if the adhocdistributedquery advanced option is turned on with sp_configure.

Note also that in sql server 2008 R2 x64 this works properly against Excel files and fails only against Access mdb or accdb database files (it used to work against access database files in sql server 2005 and possibly also in 2008--I forget if I tested in sql 2008). This issue occurs when access 2010 database engine files are installed, 32bit or 64bit.


Details
Sign in to post a comment.
Posted by Microsoft on 5/20/2013 at 10:27 AM
Hello,
After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server.  The reasons for closing these bugs are following:
1.       The fix is risky to implement in the current version of the product (service packs)
2.       Scenarios reported in the bug are not common enough
3.       A viable workaround is available
 
Thanks again for reporting the product issue and continued support in improving our product
--
Jos de Bruijn
Posted by Transistor1 on 12/23/2011 at 2:50 PM
I confirm that TechVsLife2's workaround below worked for me (SQL Server 2008 r2 64-bit)

Update: besides using msdasql, it works, in some cases, to do the following before creating the linked server:
right-click and select "properties" for "Microsoft.ACE.OLEDB.12.0" under Server objects, Linked servers, Providers in SSMS. Check "nested queries" and "allow inprocess." Then create the linked server for access accdb using the ACE.OLEDB.12.0 provider, with the full access acccdb path and filename under data source.
Posted by TechVsLife2 on 12/22/2011 at 8:50 PM
Added a possible workaround using oledb.12.0.
Posted by TechVsLife2 on 12/22/2011 at 5:59 PM
Update: tested but this still does not work with Sql Server 2012 RC0: I am unable to create a (functional) linked server to Access 2007/2010 database files (using the recommended Microsoft ACE.OLEDB.12.0 provider).
Posted by MarkEmery on 8/17/2011 at 8:57 PM
Got this to work in SQL 2008R2 on Windows Server 2008R2:

EXEC master.dbo.sp_addlinkedserver @server = N'EMPLOYEELIST', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'E:\ADUpdates\list.xlsx', @provstr=N'Excel 12.0'

to query the Excel sheet labeled EmployeeData:

SELECT * FROM FROM [Employeelist]...[EmployeeData]

Cannot connect to Access DB with similar syntax using Access as Provider.
Posted by TechVsLife2 on 7/19/2011 at 10:09 PM
Expyram,
Can you describe what steps you took when logging in using the service account? I don't get that working, assuming I followed the same steps. (are you using a standalone machine or a domain?) However the workaround listed by Rattus Nor is working. thanks.
Posted by Expyram on 6/10/2011 at 1:22 PM
Running SQL 2008 R2 x64 on Windows Server R2 SP1 x64. Installed the x64 Ace drivers. I was unable to create linked servers, or execute OPENROWSET queries, receiving, depending on the approach, the various error messages "Could not find installable ISAM", "Unspecificied Error", and others. I could successfully do all these things from x86 servers; in fact, I could successfully execute OPENROWSET queries on the x64 server when using SSMS remotely from a x86 machine with the x86 Ace drivers.

My solution was to log into the server once using the service account for SQL Server. Everything worked immediately thereafter, and I replicated this on a second server.

I read elsewhere that the Ace and Jet engines both require access to the Temp folders to do their work. I suspect logging in once was necessary to "wire-up" access to the Temp folder.
Posted by MarkEmery on 1/27/2011 at 9:13 PM
To clarify, Select ROWSOURCE query works for Excel 2010 .XLSX files but linked server object for same file does not:

EXEC master.dbo.sp_addlinkedserver @server = N'TEST', @srvproduct=N'Excel 12.0', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'E:\ADUpdates\Employee List.xlsx'


Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST" returned message "Unrecognized database format 'E:\ADUpdates\Employee List.xlsx'.". (Microsoft SQL Server, Error: 7303)
Posted by MarkEmery on 1/27/2011 at 9:05 PM
Excel 2010 .XLSX don't work either as a linked server object in SQL 2008R2
Posted by prashant patole on 1/19/2011 at 3:30 AM
Me too facing same problem.
my configuration is
WIN 2008 X64    
SQL 2008 R2 X64
Access 2003 x86
the ACE drivers mentioned below didnt helped.

Posted by TechVsLife2 on 11/17/2010 at 12:13 PM
@Pooja Harjani,
Also note that the thread you mention is for Excel files. There is no problem with Excel files. The issue is reading Access accdb files ONLY: sql server x64 is not able to link against them using ANY version of the ACE drivers, 32 or 64 bit.
Posted by TechVsLife2 on 11/17/2010 at 12:07 PM
@Pooja Harjani,
Thank you. The problem is that **WITH X64 ACE drivers** installed, the linked server feature is not working. If you see my problem report, I have office 2010 x64 installed, and access 2010 x64 is installed (with the ace x64 drivers).

This is a major bug in sql server 2008 r2 x64, confirmed by Jin Chen: the linked server feature to access database files does NOT work in sql server x64 with access x64 drivers installed. (although it works to excel files).

Note that it ALSO does not work with 32-bit drivers from x64 sql server. There is NO linked server feature working correctly from x64 sql server to ace/access/accdb database files using any driver. (but it works to excel files).
Posted by Microsoft on 11/17/2010 at 11:38 AM
Hello,

Thank you for reporting the issue. Installing the x64 ACE driver should help here. You can find it at http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d?

You can also find another thread here http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/9bcda4ad-7264-4919-a35b-d28ce41a521f

Thanks,
Pooja Harjani
Program Manager
Posted by mirsk1 on 9/1/2010 at 3:16 PM
Also, when trying to import directly using Data Import alhough it sees all the tables to import, when we run the SSIS package the error we get is

- Setting Source Connection (Error)
Messages
Error 0xc020801c: Source - Account Types [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
(SQL Server Import and Export Wizard)

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
Posted by mirsk1 on 9/1/2010 at 3:06 PM
Similar but different error when using the OpenRowSet against the same MS Access Database.
I am able to see column headers in the grid results so it is talking to the database table at some core level but it does not populate the grid and produces the error below.

Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

When trying the linked server approach there are no grid results, just the error 7303 error above
Posted by mirsk1 on 9/1/2010 at 2:50 PM
Exact same scenario duplicated on WINDOWS SERVER 2008R2 x64.

Running SQL2008. Installed Access engine 2010 to get around the single apartment mode issue and now stuck on the

Could not find installable ISAM.". (Microsoft SQL Server, Error: 7303).

Need a feasible solution to reading MS Access via either linked server, openrowset, and/or opendatasource. All fail with similar errors. Also have same issue expanding catalog default tables on linked server as described above.

Are there any suggestions from Microsoft at this time other than installing a 32 bit instance of SQL on this server ?
Any insight would be greatly appreciated.
Sign in to post a workaround.
Posted by TechVsLife2 on 1/10/2012 at 10:26 PM
The "/passive" switch turns out to be a bad idea, as it creates a conflict and subtle bugs, at least if you try to install both office 2010 32bit and 64bit drivers, so I don't recommend it.
Posted by TechVsLife2 on 12/22/2011 at 8:52 PM
Note in step 1 from Rattus Nor above, if you are running 32-bit office and 64-bit sql server, then you won't be able to install 64-bit access engine unless you use the /passive switch. (That may not be an officially supported configuration by microsoft.)
Posted by TechVsLife2 on 12/22/2011 at 8:49 PM
Update: besides using msdasql, it works, in some cases, to do the following before creating the linked server:
right-click and select "properties" for "Microsoft.ACE.OLEDB.12.0" under Server objects, Linked servers, Providers in SSMS. Check "nested queries" and "allow inprocess." Then create the linked server for access accdb using the ACE.OLEDB.12.0 provider, with the full access acccdb path and filename under data source.
Posted by TechVsLife2 on 7/19/2011 at 8:52 PM
thanks, seems to work. One side effect is that every accdb/mdb file in that folder is listed under the linked server object in ssms (under Ctalogs).
Posted by Rattus Nor on 5/13/2011 at 7:59 AM

My "workaround" to get to mdb/accdb data is to use the ODBC version of the driver and a System DSN on the computer running SQL Server 2008 R2 (64-bit version).

1. Download and install the 64-bit version of the Microsoft Access Database Engine 2010 Redistributable from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d on the computer running sql server.


2. Open the ODBC Datasource Administrator on the computer running sql server (you may search for this dialog through Help and Support). Select System DSN, then Add. In the Create New Datasource dialog, select the driver named "Microsoft Access Driver (*.mdb, *.accdb)" version 14….. Select Finish. Type a name for the new data source, say "MyAccessDatasource", then select the leftmost button (Select?) under Database to indicate your mdb or accdb file. Select OK twice.


3. In Microsoft Sql Server Management Studio, select Linked Servers/New Linked Server. Type in a name for it after Linked server, say "MyAccServer". From the provider list, select Microsoft OLE DB Provider for ODBC Drivers. After Product name, type in something, for example Access. After Data source, type in the exact name of the data source created in step 2 above, say "MyAccessDatasource". Select OK.


4. To query for the data in a table in the mdb or accdb file, say in a table named "MyAccTable", using the linked server ("MyAccServer"):

SELECT *
FROM OPENQUERY(MyAccServer, 'SELECT * FROM MyAccTable')