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.
- Download and install an x86 version of Sql Server 2014.
- Download and install the OLE DB FoxPro drivers.
- Add a FoxPro Linked Server to the x86 Sql Server instance
- Server Objects -> Add Linked Server …
- Provider: Microsoft OLE DB Provider for Visual FoxPro
- Product Name: VFPOLEDB
- Data Source: Set the path to the folder containing the FPT and DBF files.
- 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.
- Provider String: VFPOLEDB
- 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.
- Now you should be able to write queries like this from the 32 bit instance:
-
1select * from [LinkedServerName]...[FoxProTableName]
-
- Server Objects -> Add Linked Server …
- 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.
- Congratulations! You just queried FoxPro data from Composable.
- Deploying Composable Resources via Powershell - December 1, 2022
- Composable APIs via Powershell - December 1, 2022
- Enabling Long Paths in Composable - April 6, 2022