Thanks Tamar.

On the first item, I searched the help file for varchar(max) but did not find anything. Is that what you were meaning?

Regarding large tables, I had thought about that - the current app allows users to browse and do incremental search on the key tables but that will probably have to go away. The largest one has about 50,000 client records and it still works fine for speed but the users have already said they would like to see this change because now with 20+ years of client records, many are old or closed and so they would rather not see them anyhow so I had planned to filter this at some point to only show active clients). I could probably even implement this change before doing any SQL conversion and so they will see this as a new and improved feature to VFP instead of something to blame on SQL server.

Regarding deleted records, they do indeed have a utility to restore deleted "quotes" for someone - occasionally someone deletes all the old quotes from someone in anticipation of closing the file. Would have to rework into it being a flag instead and by default filter them out. Good to know as that is going to involve some work.

A new question: I take it that data could probably exist in both places as I go through the conversion (to a limited degree) - as long as the query is on a single table, it could be local or it could be on the server? correct? the only problem I would run into would be if the query was against more than one table.

Albert



>>Follow up questions interspersed below:
>>
>>>1. SQL Server has no MEMO or GENERAL field types, but it has n/varchar(xxxx), varbinary(xxxx) field types instead.
>>
>>- doing a quick test, it seems varchar is limited to 254 bytes. What actually translates to a memo field of indeterminate length? The app uses memo fields fairly extensively for notes about companies that this company deals with etc.
>
>Check out varchar(max) to replace memos.
>
>>
>>- follow up question: this app contains accented characters for some fields (thought triggered while reading up on varchar/varbinary): do the code pages match up between SQL and VFP?
>
>Yes, that's a Windows level feature.
>
>>
>>>2. No, all queries from SQL Server come as CURSOR/DataTable/DataReader.
>>
>>- so I guess one would have to bring down the data to a cursor and then do a 2nd query against the local cursor to throw it into an array...correct?
>
>Yes, you could. I'd suggest you think in terms of isolating all your communication with the database into a class (or classes) and provide existing code with exactly what it currently uses.
>
>One caveat there is that in VFP, we're generally used to having very fast access to even the largest tables, so we don't think twice about throwing a whole table, say, into a grid. With a SQL back-end, limiting the data you pull from the server to only the relevant fields and records is key to good performance.
>
>>
>>>3. RI should be handled by backend, because nobody can guarantee that the data can be modified only by your front end. Somebody can use SSMS to modify records.
>>
>>- in this case, the app does not use the built-in RI engine as it was too "hairy" - there are some cascade deleted in their system where the delete can only go ahead if certain conditions are met (e.g. they have the ability to "lock" (flag) some records once something happens on the record - but I guess these could be handled by an initial query against the table and then if conditions are met, go ahead with the delete
>>
>>- follow up question: do deleted records stay in the SQL tables for recovery if necessary? I would expect that this might be more of a function of database maintenance/purging that is configured hopefully on a table by table setting.
>
>No. In pretty much every other database, DELETE means delete and there's no way to recover a record. If allowing recovery is an important feature for you, you'll need to implement via a field that indicates whether a record is current or not. Similarly, any strategy of recycling records is not recommended for SQL back-ends.
>
>>
>>>4. No, you can use the same PKs.
>
>
>To start getting a handle on all this stuff, I'll recommend http://hentzenwerke.com/catalog/csvfp.htm. However, it predates the VFP cursoradapter class, so you'll want to read about that separately, as an option for communicating with SQL Server.
>
>Tamar