You should be using SQL Server's TRANSACTION to make sure the tables are updated correctly.

Here is some sample code:

Local liDataConnection, lUpdSuccess

* Get your global connection handle or create one if necessary
liDataConnection = goApp.iDataConnection

* Begin SQL Server Transaction
SQLExec(liDataConnection,'BEGIN TRANSACTION')

* Update your tables here
lUpdSuccess=tableupdate(.t.,.f.,"mytable1")
If lUpdSuccess
tableupdate(.t.,.f.,"mytable2")
Endif

* Rollback if not successful or commit changes
If lUpdSuccess
SQLExec(liDataConnection ,'IF @@TRANCOUNT > 0 COMMIT')
Else
SQLExec(liDataConnection ,'IF @@TRANCOUNT > 0 ROLLBACK')
Endif


>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