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.