I am running into a similar issue with an Application written in FoxPro that I am trying to link to in SQL 2005. I have created a linked server and pointed it to the System DSN that I created, however I receive an error message stating the file I'm trying to query does not exist. Here's the particulars.
Windows 2003 Standard SP1
File I'm trying to link to was created in Abra, called hrPersnl.dbf (There is also a CDX file).
System DSN: Name is hrPersnl and it is set to "Free Table directory" and pointing to a mapped network drive "r:\Programs\Data"
Linked Server Information:
Provider: MS OLE DB Provider for ODBC Drivers
Product Name: hrPersnl
Data source: hrPersnl
Provider string: (empty)
When I try to Query the server, using select * from openquery(hrPersnl, 'select * from hrPersnl') and it generates the following error:
OLE DB provider "MSDASQL" for linked server "hrPersnl" returned message "[Microsoft][ODBC Visual FoxPro Driver]File 'hrpersnl.dbf' does not exist.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "hrPersnl".
Let me know when you can and I greatly appreciate your help on this issue and the valuable information you post here regularly.
Please download and install the FoxPro and Visual FoxPro OLE DB data provider from msdn.microsoft.com/vfoxpro/downloads/updates .
When you set up your linked server see if there is a Database Container (DBC file) in the directory where the hrPersnl.dbf is located. If there is a DBC, point your linked server to that, otherwise stick with the free table option. The CDX file is a FoxPro index. The Fox data engine uses it automatically as appropriate; you don't need to worry about linking to it. You may also see FPT files which hold the contents of Fox Memo fields if there are any in the table. Again, the data engine takes care of accessing the FPT file automatically if it is present.
In your linked server use whatever you want for the product name, for the Data Source use either something like "C:\Temp\VFPTest\" for free tables or "C:\Temp\VFPTest\MyDBC.dbc" for a Database Container. Surround the Data Source with quotes if there are spaces in the path. The Provider String should be VFPOLEDB.1 . You can leave Location and Catalog blank. In code this would be something like:
@server = N'VFP_NORTHWIND',
@srvproduct=N'Visual FoxPro 9',
@datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\Samples\Northwind\Northwind.dbc"',
You can access your table with code like Select * From VFP_Northwind...Customers . Please note the 3 dots as the Catalog and Schema are empty.
I am not familiar with Abra. If you import everything from the Abra file into a SQL Server database you will not have any new data that is added to the table, so if having real-time data is important I'd go with the Linked Server; if you just need setup data to start with, importing the data sounds OK.