An application for one of my clients shares the Customer table with their accounting system. Both applications have co-existed peacefully using FoxPro 2.6 tables for many years. I fear that's about to end. The accounting system is being upgraded to a new version with the customer table in a VFP7 DBC. At the same time, I'm converting the custom application to SQL Server 2000 using a combination of SPT and remote views. I find myself needing to select data from the VFP7 table based upon values in the SQL table or vice versa. The VFP 7 table was added as a local view to the DBC containing the remote views. Joining the data in a single query or view doesn't seem to work -- at least not in any way that can be reproduced or managed under program control.

For example I would expect either

SELECT a, b,c from where c in (SELECT c from )

OR

SELECT Remote.a, Remote.b, Remote.c, Local.d FROM RemoteView, localView WHERE local.c=remote.c AND remote.x = ?

to produce a result set with multiple rows. Neither does. Trying to combine the data sources in a new local view causes an easily-reproducible Fatal Error so I surmise this isn't supported <g>

Using the IDE, I can load the entire local table and the entire remote table (no parameters on either) into separate workareas and then execute a query similar to the above and achieve the desired results but that's not feasible in a production environment.

How do other folks handle this?