Sql-server – How to successfully connect to Foxpro database files using MSSQL Linked Server feature and ODBC

erplinked-serversql server

We're trying to access Foxpro file-based database files via MSSQL Server's linked server feature, using Foxpro ODBC driver.

For this, we've

  • installed the latest Foxpro ODBC Driver from Microsoft,
  • made a System DSN which is using this driver
  • chose the "Visual FoxPro database (.DBC)" option under Database type, instead of "Free table directory"
  • specified the path to our dbc file (on local machine)
  • installed MSSQL Server 2008 R2 Express
  • installed MSSQL Server Management Studio 2008
  • tried to create a new linked server by using a GUI wizard like this:
    New Linked Server screenshot

But the last step never completes (Executing is displayed forever).
When Management Studio is forcibly closed and restarted the new linked server is there but only contains Catalogues subitem. If we try to expand it, Management Studio goes into loop yet again.

Best Answer

  • Download and install Microsoft OLE DB Provider for Visual FoxPro
  • Make sure it appears in MSSQL Management Studio, under System Objects > Linked Servers > Providers, as "VFPOLEDB". I had to install it, uninstall it and re-install it until I got it there (and no, restarting Windows did not work).
  • Open System Objects, right click Linked Servers and choose to add a new linked server.
  • Fill the form as like this:screenshot of New Linked Server with proper values for Foxpro connection
    • No need to add any DSNs in ODBC applet
    • Click OK and see if you can now drill down the new linked server until you see the tables.
    • Right click on some table and script a SELECT query to new query window. This gives you an example how to query this new linked server:
      SELECT * FROM [myLinkedServer]...[myTable]
Related Topic