Thanks for your responds. You have given a lot to consider.


>Greg,
>
>>The application is a point-of-sales application. And a few of our clients are quickly near the 2gig table size limits.
>
>Are you thinking that you can define RVs to SQL Server and then just USE them like you USE the fox tables now (creating indexes as needed)? If your reason for converting to SQL Server is that some clients are near the 2-gig size limit, you might be in for a big performance surprise unless you are planning to take a different approach that pulls just small sets of data.
>
>You can, of course, use progressive fetching settings on the views, but the performance issues may depend a lot on just what your programs do against the VFP tables when you USE them. If, for instance, you have code that jumps around in a table by doing seeks against an index (and you want to preserve that code by doing a USE view instead of USE table), you'll have to have ALL the data pulled down into the cursor so the index can be built so you can do that seek. With lots of records, this is going to be a problem, because once you bring down all that data to the local cursor to index it, you immediately have the risk of the data becoming stale as other users update records in the backend database.
>
>Unless you plan to change how your program handles the USE, SEEK, LOCATE, GO *recno* scenarios to something that pulls just the record(s) you need when you need them, you may be biting off some major problems.
>
>That being said, I have successfully handled a VFP to SQL Server conversion that involved data from 40+ locations into a centralized database and used CursorAdapters to provide the VFP cursors for the modified code to work with. (CursorAdapters are very similar to RVs except that they are subclassable with many event hooks like the AfterCursorFill() already mentioned by someone else).
>
>The original app was designed to step a record at a time through the customer file (as many old apps did), and the customer did not want to drastically change that approach due to the need to preserve as much code as possible. So, we actually designed some methods in our top-level CursorAdapter class for GetNext, GetPrevious, and so forth, which used TOP n queries for a certain "where" condition and "order by" setting in combination with a passed-in PK of the "current record". Even against extremely large tables, it performed very well. (The SQL purists will probably gasp in horror at single-record paging, but it's really not much different that stepping a "page" at a time through a SQL database, pulling the next n records as is often done in web apps).
>
>For records related to the "current record", we used parameterized queries via CursorAdapter to pull just the records we needed from other tables (such as for line items matching a header record).
>
>It was quite a big project to convert everything, but the project was completed in less than half the time a completely rewrite would have taken. For some parts of the app, we used a new search form capability that gave the customer new ways to query into the records instead of pulling the entire table into a browse, etc.
>
>Shoehorning SQL Server into an existing VFP app CAN be quite easy if the app is already designed around SQL SELECT calls instead of moving around directly in the tables via SEEK, SKIP, and so forth. So the level of pain in your situation will relate very much to how your app relates to the tables it has USEd and just how large those tables are.