Fox Pro to SQL 2

[yuchieh]

We have Fox Pro tables in the file server (we use FoxPro5.0). We would like to call up the data in
FP tables from SQL Server7 becuase it's more secure, so I was told. Then I can use Cold Fusion to retrieve
data from the database and create web sites for users to access to, without touching the original tables
in FP. I don't think "upsizing" is the right method for this because that would just convert the whole
database into SQL, which we don't want either.

Does anyone know what other methods to use?

I am new for Fox Pro. I will appreciate if anybody can give me more detailed information.

Thanks in advance.

 

[fluteplr]

I had a similar problem for a client. We attached the foxpro data as a remote server, and then created a stored procedure that updated the data into sql server tables that had been created with the same structure.

This procedure was then scheduled to run every 15 minutes to update the sql data. This was recent enough for their requirements and since we were able to optimize the update to run in about 30 seconds the downtime was minimal.

I don't have the code here but one thing I remember was inserting the data from Foxpro into staging tables in SQL Server and then updating the tables that were actually used to respond to the queries totally within sql server, this cut the time the query tables were locked by the update considerably.

 

[foxdev]

If you don't want to import the data, then use Distributed Queries (the neatest thing since doorknobs, when it works). You can perform a query, including joins, using pretty much any ODBC data source, including DBFs. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[yuchieh]

Thank you both. I will try both and see what's going on.
I believe I will come up more questions later.

Thanks for the information.

 

[yuchieh]

Hi Robert,
I tried to create the linked server for distributed queries. But the OLEDB provider drop down list is empty. I am not sure how to install that on the server. Also, should I choose Jet 4.0 OLEDB provider or OLEDB for ODBC provider?
After I set up the linked server, what do I do next?

Thank you for the help.

 

[foxdev]

I'm not sure why your provider list is empty; if you go to Control Panel/ODBC, do you see a list of drivers? You'll need a driver for VFP.

As an alternative, you can try the OpenRowSet command, which allows you to specify the options in-line.
Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[yuchieh]

In our live server, there is a drop down "provider name" list in the "New Linked Server" window.

The list includes -
MS Jet 4.0 OLEDB provider
MS OLEDB Provider for DTS Packages
MS OLEDB Provider for Indexing Service
MS OLEDB Provider for Internet Publishing
MS OLEDB Provider for MS site Server Search
MS OLEDB Provider for ODBC Driver
MS OLEDB Provider for Orale
MS OLEDB Provider for SQL Server
MS OLEDB Simple Provider
OLEDB Provider for MS Directory Service

Which one should I choose for Fox Pro datasource?

Thank you.

 

[foxdev]

Use the one for ODBC Driver. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org