Thanks Vlad,

I use a lot of VFP functions such as STR, DTOS, CTOD in the where clause of my SQL select statements. When I switch to remote views, will I have problems? If yes, what are the alternatives.

Eg. I have a table XYZ with primary key Period and another table ABC with primary key , patient_id and a non key field called visit date. ABC is related through cperiod. My SQL statement goes like this :

SELECT * FROM XYZ,ABC WHERE LEFT(DTOS(visit_date),6)=cperiod

Would the above statement works when I upsize to SQL SErver? Else should I create another field cperiod into ABC but then cperiod is fully derivable from visit date and that we're suppose not to have calculated fields in our table.

Thanks and look forward to your guidance.

Best REgards
>Hi!
>
>There are a lot of them. First thing you should remember is that SQL Server application interface always different than the pure VFP application database. Such application interface should be built to do not get more than 1000 records from the server - the best approach is to make search for as a starting form.
>
>Another thing is learning of the SQL Server. This way you will know all differences, so write your VFP application properly.
>
>DO not use tables directly. Do not use SEEK, LOOKUP, LOCATE for tables. Use indexed views if you still need these commands.
>
>Finally, for upsizing application, you will require changes anyway, if you use SELECT statement in your application. I know that it is bad idea to use views everywhere, because huge amount of views slow down development and application running. However, views are the best thing for upsizing that is easy to program.
>
>The best approach from the application organization, however, is to use some data manager object that will open and query all data for you. This way application will not be depended on the data source - for VFP you will use views/SELECT statements running using &, for SQl Server you will use remote views and SQLEXEC() function to run a single query. The advantage of this approach is that you will not need to change the interface application, only change the object that queries data and opens views.
>
>There is also strong warnign to do not use empty date. If you want empty date in your application, you require to convert it to null value. This is because SQL Server do not have such thing as empty date. Later, when upsizing, this problem pops up and takes a lot of time to fix application to work with null date as with empty date. Make your interface work with null date from start, so no problems later with that.
>
>You cannot use functions in the SQl Server. However, there is a great advantage - you can send a batch fo the commands to the SQL Server and get a result from the last query in it. This way you can write programs and send them to SQL Server for running as a single string in SQLEXEC command. I do not know exactly, but SP in SQL Server also can return the recordset with a single value. SELECT statement can use the SELECT as a field. So, I guess somethign like UDF should be possible for SQL Server too. Try to contact with SQL Server gurus. Personally, I beleive that good organization of the database remove all needs in UDF, complex data procesisng etc.
>
>HTH.
>
>>I intend to write application based on local views which will be upsized to SQL Server or Oracle. Is there any things I should not do that may cause compatbilty problem when I upsize later?
>>
>>Eg. Can I used function in my SQL such as :
>>
>>SELECT patient_id,lastvisit(),.... where lastvisit is a UDF.
>>
>>Regards