Q
Problem solve Get help with specific problems with your technologies, process and projects.

Schedule autonomous transactions in SQL Server 2000

How can I schedule autonomous transactions in SQL Server 2000? I want to use this in my procedures just like in Oracle.

How can I schedule autonomous transactions in SQL Server 2000? I want to use this in my procedures just like in Oracle.
Autonomous transactions are Oracle's implementation of multi-server transactions that may commit and/or rollback on each server independent of the other servers. Autonomous transactions are invoked through the command PRAGMA AUTONOMOUS_TRANSACTIONS.

You can accomplish similar functionality on SQL Server by first enabling the Distributed Transaction Coordinator (DTC) service to run on the server. Once DTC is running, you can use one of two commands to start a distributed transaction:

  1. BEGIN DISTRIBUTED TRANSACTION -- to start a single distribute transaction
  2. SET REMOTE_PROC_TRANSACTIONS {ON | OFF} -- to enable distributed transactions for all transactions started by the session
The bottom line, though, is that Oracle's transactions are rather more autonomous than SQL Server's using the DTC, since the DTC is mostly a two-phase commit tool. To have truly autonomous transactions on SQL Server, you can use these alternatives:
  1. Call a procedure on the remote server to perform your transaction.
  2. Launch a DTS job on the remote server to perform your transaction
  3. Create a linked server and then execute your transaction against the linked server.
However, each of these additional alternatives have one or more drawbacks. Read about remote servers to get a better feel for which option is best.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close