Advertisement
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
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.
