how to link visual fox pro dbf to the MS SQL 2005 in real time

Posted on 2012-09-06
Medium Priority
Last Modified: 2012-09-24
Hi Experts,
I have some visual fox pro dbf file which is being used by the users. I need to link the all .dbf file with the MS SQL 2005 to get data in real time and create some new store procedures.
Is there any way?
Please help
Question by:Mehram
LVL 75

Accepted Solution

Aneesh Retnakaran earned 2000 total points
ID: 38373310
This is coming really late, but the following process worked for me when trying to query DBF files using only one linked server and ODBC connection.

Installed Visual FoxPro ODBC drivers on server. (http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx)

Installed Visual FoxPro ODBC bugfix on server. (http://support.microsoft.com/kb/191685/EN-US/)

Setup System ODBC connection to point to local directory containing DBF files. I used the "Free Table Directory" option in the setup. Also, I could not get it to work over a share. IMPORTANT: The files had to exist on the server running SQL server.

Setup a linked server on the SQL server using the following properties:
Linked server - [Name of linked server]
Provider - "Microsoft OLE DB Provider for ODBC Drivers"
Product Name - "Microsoft Visual FoxPro Driver"
Data Source - [Name of your System ODBC connection]

After this the following query worked perfectly:
Select * from OPENQUERY('Select * from {filename}')

Even better, the following one worked also!
Select * INTO {TableName} from OPENQUERY('Select * from {filename}')

As you can see, any filename can be used dynamically within the query, allowing for only one Linked Server and ODBC connection.

Source :  http://www.sqlservercentral.com/Forums/Topic285021-146-1.aspx#bm639303

Author Comment

ID: 38373359
Sir, thanks for the detail help. Is it same way for foxpro 4 dbf?

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question