Transactions

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.