I have 2 remote views in which both remote views must be successfully updated or the both must be rolled back.

The underlying tables are either in a VFP database or SQL Server database. The remote views are in a separate database on the workstation with a ODBC connection to the remote data.

Both underlying tables start out with zero records. When I insert a record in each RV, and the second one fails to update, both should revert to zero records in the underlying tables. That is not happening. See comments in code.

use v_fldmon1
=cursorsetprop("buffering",5,"v_fldmon1")
*!* Remote view v_fldmon1 is empty 
select 0
use v_fldmon2
=cursorsetprop("buffering",5,"v_fldmon2")
*!* Remote view v_fldmon2 is empty 

select v_fldmon1
*!* insert a good record into v_fldmon1
append blank
gather memvar
*!* Remote view v_fldmon1 now has 1 record not yet updated. 

*!* cause an error that will prevent update of second RV
select v_fldmon2
append blank
gather memvar
replace fldmon2id with .null.  
*!* Primary key set to .null. - this will cause the second view fail on tableupdate
*!* Remote view v_fldmon2 has 1 record with an error to prevent update for testing

begin transaction
if tableupdate(1,.t.,"v_fldmon1") .and. tableupdate(1,.t.,"v_fldmon2")
   *!* both remote views updated correctly
   end transaction
else
   *!* one or both remote views failed to update underlying tables
   rollback
   =tablerevert(.t.,"v_fldmon1")
   =tablerevert(.t.,"v_fldmon2")  
endif 
select v_fldmon1
browse 
*!* Remote view v_fldmon1 is empty (rolled back)
select v_fldmon2
browse 
*!* Remote view v_fldmon2 is empty (rolled back)
=requery("v_fldmon1")
*!* Remote view v_fldmon1 has 1 record that was updated to underlying table
=requery("v_fldmon2")
*!* Remote view v_fldmon2 is empty because tableupdate failed due to null primary key

I thought that I could use a transaction, but the transaction only rolls back the data in the remote views and the first underlying table is still updated with the new record. I need for the record in the first remote view that succeeded on tableupdate() to be rolled back/removed when the second tableupdate fails.

What an I doing wrong? The above code works correctly when I access the underlying VFP tables directly but not when using remote views.

Can I NOT use a transaction with remote views? How do I ensure that if either remote view fails to be updated, both will be rolled back and the underlying tables will not be updated?

TIA

Elmer