none
SQL 2005 Linked Server to VFP tables

    Question

  • Cindy,

              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.

    SQL2005 Standard

    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)

    Location: R:\Programs\Data

    Catalog: (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".

     

    Have you ever tried linking to a DBF that was created in Abra before?  Also, can you see anything that I am doing wrong here?  All I'm trying to do is query the information to build a user login portal in ASP.  I have thought about just importing everything from the Abra file into a SQL DB for ease of use.  Also, I have read a lot about allowing the "Openquery" feature in the Surface Area Configuration, which I did.

     

    Let me know when you can and I greatly appreciate your help on this issue and the valuable information you post here regularly.

     

    Charles Norman

    Friday, November 17, 2006 2:22 PM

Answers

  • Hi Charles,

    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:


    EXEC master.dbo.sp_addlinkedserver

      @server = N'VFP_NORTHWIND',

      @srvproduct=N'Visual FoxPro 9',

      @provider=N'VFPOLEDB',

      @datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\Samples\Northwind\Northwind.dbc"',

      @provstr=N'VFPOLEDB.1'

    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.

     

    Friday, November 17, 2006 3:02 PM