Accessing FoxPro data files from SQL Server with Link Server

Problem:

My management gives assign me a task to find a way to communicate between two software. There is a finger tech application that record finger attendance and other application is .net based CRM. CRM has SQL Server as its backend but finger tech application is built in some other technology that needs to communicate with SQL Server to send data into CRM database.

Solution:

I tried to find a solution by reading articles for Finger tech software but did not get any good solution. Only solution I found is we can export data from application using ODBC connection into SQL Server but that is a manual process.

While looking at its data files I came to know that it can be access or FoxPro files. I tried everything with access to export data into SQL Server or Import data from access using SQL Server. Then there is a blog that mention dbf files are FoxPro files and there is a provider available on Microsoft that needs to install to connect with SQL Server using SSIS or link server. I prefer link server because I need to add data from live database instead of some backup.

Step1: Microsoft OLE DB Provider for Visual FoxPro 9.0 

The Visual FoxPro OLE DB Provider (VfpOleDB.dll) exposes OLE DB interfaces that you can use to access Visual FoxPro databases and tables from other programming languages and applications.

http://www.microsoft.com/en-us/download/details.aspx?id=14839

Step2: Installing or upgrading OLEDB Provider

You can download this provider from Microsoft download center

On completion of downloading package you can find these two files in download folder.

After downloading package we need to run the setup file.

Once installation is successful. You can see new VFPOLEDB in SSMS provider list

Step3: Creating Link Server using FoxPro Provider

From the Link server tab in SSMS we need to create new Link server for FoxPro

Following values needs to be added

1- Name of the link server

2- Under Other data source, need to select Visual Fox Pro provider

3- Product Name is the name of you SQL Server database

4- In Data Source you need to mention path of your data files DBF. If you want to access only one file you can gives its path otherwise you need to provider folder path to access all other tables.

5- In Provider String, FoxPro dll provider is mentioned.

6- You can also generate it using Script button from top.

7- For security point of view I have created it using Login's security context.

Step4: Accessing Tables and other data

Once Linked server created. You can view and query to your tables and view.

While trying an instance using FoxPro provider it generate an error.

Cannot create an instance of OLE DB Provider VFPOLEDB object

And here is the answer for that.

Server Objects > Linked Servers > Providers > VFPOLEDB > General tab > Provider options > Allow inprocess. 

also, you can change the InProcess setting with the following code:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'VFPOLEDB', N'AllowInProcess', 1
GO

 Thanks to this discussion in following thread

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e54d20dd-b65b-4cff-9349-6499e6e069e2/cannot-create-an-instance-of-ole-db-provider-vfpoledb-object?forum=sqlreportingservices