web analytics
Press "Enter" to skip to content

Migrating a FoxPro Database to 64 bit SQL Server

Published September 25, 2017 by Lars Fiedler
Lars Fiedler

We recently had a client who needed to query data from a legacy system built on FoxPro.  What is FoxPro you ask?  Fox Software was a company Microsoft purchased back in the early 90s, and they developed a system which accelerated the development of business / database applications.  You can think of FoxPro as a technology in between Access and VB.  It is more complex and powerful than Microsoft Access, but requires less code to write than a VB.net application.

The problem now is that  Microsoft has stopped developed of FoxPro for the last 10-15 years, and the supporting tools have been slowly decaying.  There is no longer an ODBC drive for FoxPro.  So if you want to query a FoxPro database, you’ll need to use the OLE DB drivers.  The latest drivers can be downloaded here: Microsoft OLE DB Provider for Visual FoxPro 9.0 SP2

And for any of the latest Microsoft downloads for FoxPro, go to: https://msdn.microsoft.com/en-us/library/mt490121

So what if you want to import data into SQL Server using the Data Import Wizard, or add the FoxPro database as a linked server?  Well, one issue you might run into is that the FoxPro OLEDB drivers are only 32 bit – sorry no 64 bit versions.  So if you’re running a 64 bit version of SQL Server, then you won’t be able to do it.  So what can you do?  Install a 32 bit version of Sql Server.  As of this article, Sql Server 2016 has no support for 32 bit, so you need to download an earlier version.  Sql Server 2014 has a 32 bit version.

So how are we going to get data out of FoxPro?  The basic flow is going to look like this:

FoxPro Db -> Linked Server -> 32 bit Sql Server Instance -> Composable -> 64 bit Sql Server 

We’ll link the FoxPro db to the 32 bit version which will allow us to write Sql queries against the FoxPro db.  We’ll then write a syncing dataflow in Composable to query the 32 bit Sql Server instance and insert the records in another Sql Server instance.

Here are basic steps.

  1. Download and install an x86 version of Sql Server 2014.
  2. Download and install the OLE DB FoxPro drivers.
  3. Add a FoxPro Linked Server to the x86 Sql Server instance
    1. Server Objects -> Add Linked Server …
      2. Provider: Microsoft OLE DB Provider for Visual FoxPro
      3. Product Name: VFPOLEDB
      4. Data Source:  Set the path to the folder containing the FPT and DBF files.
        1. Note that a file share may cause permission errors, so start out with the local folder that the Sql Server engine users has permissions to access.
      5. Provider String: VFPOLEDB
    2. In order for the Linked Server to work properly under a 32-bit process and with the necessary permissions, the FoxPro OLE DB Provider needs to be configured with ‘Allow inprocess’, which will execute the provider within the Sql Server process.
      2. Linked Servers -> Providers -> VFPOLEDB
    3. Now you should be able to write queries like this from the 32 bit instance:
  4. Now you just need to write a dataflow that runs the above query and inserts data into another Sql Server instance.  Note that this flow will transfer the data very efficiently – capable of moving millions of rows in just a few minutes.
  5. Congratulations!  You just queried FoxPro data from Composable.

 

Lars Fiedler
Latest posts by Lars Fiedler (see all)
Lars Fiedler

Lars Fiedler

Lars has comprehensive expertise building large complex software systems, and has served as a Software Engineer at MIT’s Lincoln Laboratory from 2010-2014, where he began developing Composable Analytics. Prior to joining Lincoln Laboratory, Lars worked as a Software Engineer at Microsoft Corporation from 2006 to 2010. Lars received his MS in Computer Science from Georgia Institute of Technology in 2004, and his BS in Computer Science from Georgia Tech in 2003.

Published in Automation and Developer Blog