>Is it possible to create some of the business rules in SQL by parameterising queries or even having sub-procedures which are client specific? An alternative approach if you have a different installation per client is to have multiple versions of the same procedure with the same name?
>

The answer to the above question is yes. But (big BUT), I am a very newbiew to SQL Server and to write such a SQL Server Stored procedure will be a huge undertaking for me. It is much simpler for me to do the processing in VFP tier and then send a table to the SQL Server for update.

>The reason I suggest this is that you don't want to be moving data off of the SQL Server into another layer unless you absolutely have to. As others have mentioned 10,000 key fields may be ok, but then are you going to call a further download per keyed row to get the data to process?, and then you have to write it back again.
>

Yes, for every key for this table the business tier will process the data and calls SQL Server to update a row.

>The other thing to consider here is the effect that this procedure will have on shared access to the data. Is it an exclusive routine for out of hours processing, or is there a risk that end users could be accessing the data whilst this bulk update is happening?

In VFP this process takes no more than 2 minutes. Say, in SQL Server it will take 10 minutes. While the bulk update/process will be happening, the application will place a semaphore lock to prevent other users from access this table. As I mentioned before, this process is done a few times a year, if that much.

Thank you for your input.