Linked server to a Microsoft Access linked table

Question

Hallo all,

I have been searching for an answer to this for a few days now.  My setup is as follows:  There is a Syspro data source (C-ISAM) which I need data from.  I need this data in a SQL Server 2005 Express 9.00.3042.00.  The company has been using Microsoft Access linked tables via an DNS ODBC link to the ISAM database to retrieve data for reports for a few years now.  It was suggested that I use this existing link.  The SQL server and MS Access database is on the same server and the ISAM database is on another server accessed over the local company network.

I first started using

OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'drive:\path\database.mdb';
      'login';'password',query)

in order to obtain records.  I had some problems with this and then tried a SQL linked server.  The linked server is linked to the MS Access database.  So we have SQL Server---MS Access linked tables---ISAM.  I am able to obtain the data I need but it is quite slow.  If I run the script via the SQL linked server with the data imported into a local MS Access database (no linked table to ISAM, all data is local to the MS Access table, the SQL server and MS Access database is on same machine) the script runs about 24 minutes.  The script takes about 7 hours when the linked tables are set up to the ISAM database.  Some of the individual ISAM tables are about a gig in size.  It has happened twice that this process slows down (although the script runs at night) such that it takes longer than 24 hours (no use in that).  I discovered that the problem was the linked server.  At first the linked servers would not delete and the SQL Management Studio would say that the server does not exist.  I created other servers with the same problem.  The next day they could be deleted and I created new ones and they worked fine.  Since then I have moved to a new workstation and the same has happened again. 

The same happened last week and I created the linked servers yet again.  This works fine now.

The latest error that I cannot find a solution for is the following:

Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

This happens only when I query really large tables.  I read on one of the MSDN forums that one should set DBCC TRACEON (7330).  Since then there is the additional message of
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "selectDB" returned message "ODBC--call failed.".

I have seen the last message before and on occasion it has helped to recreate the linked tables in MS Access.  This time that does not work.
 
The first advice that I came upon on one of the MS forums was to reduce the size of your memory paging.  That did not work. 

The next thing was to run:

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

and

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

This also does not work.  I must just note that I did not quite like the detour of going via the MS Access linked tables but neither the client staff or I was able to retrieve data with a direct link to the ISAM database using a ODBC connection so rather than waste time we went with what we had.

Hope someone can help me solve this.  Please.  Regards.

Answer 1

Sorry that I may not fully understand your meaning.

What I understand is:
- You want to create a linked server to ISAM data source without using MS Access. In this case, you may try to use MSDASQL with the existing ODBC DSN connecting to the ISAM data source. You can obtain an example about using MSDASQL in linked server at: http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx
- You mentioned the performance degraded in the scenario SQL -> Access -> ISAM (7 hours) compared to SQL -> Access (24 minutes). However, since the Access is local to the SQL Server, this may be expected to be slow to obtain remote data from ISAM. Do you have any performance data about Access -> ISAM?

Thanks,
Ming.
WDAC Team, Microsoft.

---

Pak-Ming Cheung - MSFT