>I have had a problem with VFP locking under certain circumstances when I requery a remote view for a while now. The scenario is as follows:
>
>I have a remote view, it is based on two SQL Server tables, it is parameterised, it is updatable and I use optimistic table buffering on it. The view is used to retrieve/edit basic customer info. By itself it works perfectly (and repeatedly).
>
>I have a stored procedure in SQL Server. This SP add records to several tables, including the two that the view is based upon. The stored procedure creates a new customer and by itself it works perfectly (and repeatedly), either from VFP or Sequel.
>
>I can repeatedly add new customers from VFP, filling in their details in the remote view and saving these. This works fine. I can also repeatedly call up different customers using my remote view, change information and save it. This is also fine.
>
>The problem crops up when I try to edit a customer and then add a new one. In slightly more detail, the process goes as follows:
>
>I edit a customer using my remote view, then save the data and close the view. Then I open the view again with the NODATA clause. Then I add a new customer using SQLEXEC() to fire the SP in Sequel. Then I REQUERY() the RV, having changed the variable that the RV uses to retrieve the correct customer. VFP locks up when the REQUERY() line is run.
>
>When I look in the User Activity window in Sequel, I get the following:
>
>12:Stress.dbo/AWAITING COMMAND/SDC(MicroSoft Visual FoxPro)
> Stress.dbo.Customer
> Stress.dbo.Customer
> Stress.dbo.PsnlDets
> Stress.dbo.PsnlDets
> Stress.dbo.PsnlDets
> Stress.dbo.PsnlDets
> Stress.dbo.Address
> Stress.dbo.Address
> Stress.dbo.Address
> Stress.dbo.Address
> Stress.dbo.Address
> Stress.dbo.Add_Link
> Stress.dbo.Add_Link
> Stress.dbo.Add_Link
> Stress.dbo.Add_Link
>
>All of these entries are red, and when I view the details of any of the lines, I get the string that I passed to SQL Server in mt SQLEXEC() i.e. "sp_NewCustomer 0". The symbol to the left of the #12 is a hand over a green circle, which the legend says signifies 'Blocking'. If I kill the process at the top, VFP is freed up. This has got me totally mystified. My stored procedure is wrapped in a BEGIN TRAN, COMMIT TRAN, I've even tried adding a CHECKPOINT statement at the end of the SP.
>
>The only clue that I have is that if I close all connections between editing/saving my view and then adding a new customer and doing a REQUERY(), it works fine.
>
>I would love to hear from anyone who has any ideas as there seems to be no way to debug the problem at all.


Are you using SQL BEGIN TRANSACTION?

if yes then you have to set the transaction property of the connection to 2
by using SQLSETPROP command, then issue the BEGIN TRANSACTION command,
then after commiting/rollback set it back to 1.