Keep in mind that you need to issue *two* transactions. One for VFP to look at the views and another for SQL Server to look at the tables. Take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnfoxtk00/html/ft00i6.asp


>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