SQL Server's default behavior is to automatically commit implicit transactions. In Oracle, the default is that implicit transactions require an explicit commit. However, a linked Oracle query by default automatically commits. If you want to issue a ROLLBACK statement on a heterogeneous query, you must use an explicit distributed transaction. To roll back a linked-server query, you must explicitly declare a distributed transaction before submitting the query. First, you must set XACT_ABORT to ON. Then, you use the BEGIN DISTRIBUTED TRANSACTION statement, as the following example shows:

                              SET XACT_ABORT ON                              BEGIN DISTRIBUTED TRANSACTION                              INSERT INTO oradb..SCOTT.DEPT values                                  (1,'test','example')                              ROLLBACK

When you're finished, you issue either a COMMIT or ROLLBACK, just as you'd do at the end of a regular transaction.