Setting aside the Remote Views vs SPT issue for the moment, why do you rely so heavily on Stored Procedures?
SPT can be used in the middle tier for Updates, Inserts, and Deletes and integrated with business logic.
Also with MTS (something I'm currently looking into), the SetComplete and SetAbort functions can make Transactions
much easier to handle. With Stored Procedures, this could be a nightmare with multiple databases / backends and could affect scalabilty.
Also, clients who have a current or
potential need for SQL Server dread the idea of hiring a DBA for SP coding and maintenance - they want the developer to do it all *bg*. While a DBA may be required for other reasons, IMHO, application-specific code (e.g. Stored Procedures) should be handled be the developer.
While there are performance gains associated with SPs, I think it's a bad idea to rely on them to the extent you have indicated here and on many other past threads.
>Remote Views, at best, is an incomplete wrapper around SPT. RV's require you to ceded to much control. Further, if you are going to employ best practices with respect to SQL Server Development, you will use Stored Procedures to Update, Insert, Delete, and Query Data. Stored Procedures provide the level of security and control that developers and DBA's need.
>
>That said, RV's do not support Stored Procedures.
>
>I am a member of a team that successfuly deployed a VFP/SQL application that was migrated from an existing FPW application. Here are the stats:
>
>500+ users (simultaneous)
>100+gb database
>24x7
>Stored Proc - Driven
>No DBC or Remote View Use - use SPT and ADO instead....
>
>Remote views do not scale....period.
>
>If you care to chat about this privately, send me an e-mail...
>
>
>
>>We have just installed our first SQL Server and plan to migrate current VFP applications.
>>Can someone provide me with a resource or suggestions on when to use remote views and when to use SQL Passthrough
>>
>>
>>Thanks
>>Gary in Cloudy Dallas
- Jeff