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.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish