Distributed queries and distributed transactions

Microsoft & reg; SQL Server & #8482; allows you to create a link to an OLE DB data source called a linked Server. After connecting to the ole db data source, you can:
Reference a row set from an ole db data source as a table in a Transact-SQL statement.
The command is passed to the ole db data source and contains the result row set as a table in the Transact-SQL statement.
Each distributed query can reference servers with multiple links and update or read the servers with each link separately. A single distributed query can perform read operations on servers with certain links and update the servers with other links. Generally, Microsoft SQL Server requires the corresponding OLE DB provider to support distributed transactions whenever a transaction may update data on multiple linked servers. Therefore, the types of queries supported on the linked server depend on the level of transaction support in the ole db Provider. Ole db defines two optional interfaces for transaction management:
ITransactionLocal supports local transactions in the ole db data source.
ITransactionJoin allows the provider to join distributed transactions that contain other resource managers.
All the providers that support ITransactionJoin also support ITransactionLocal.
If the distributed query is executed when the connection is in the automatic submission mode, the following rules are applied:
For providers that do not support ItransactionLocal, only read operations are allowed.
For providers that support ITransactionLocal, all update operations are allowed.
The master SQL Server automatically calls ITransactionLocal in the Server that is linked to each update operation to start local transactions and submit the statement when the statement execution is successful or roll back when the statement execution fails.
If the distributed query is executed for a distributed partition view or when the connection is an explicit or implicit transaction, the following rules are applied:
For providers that do not support ITransactionJoin, only read operations are allowed. No transactions or providers that only support ITransactionLocal cannot participate in the update operation.
If SET XACT_ABORT is SET to ON, All update operations can be performed ON any provider that supports ITransactionJoin. The master SQL Server automatically calls ITransactionJoin from each linked Server involved in the update operation to register the Server in distributed transactions. The ms dtc then commits or rolls back when the master server says you want to commit or roll back the transaction.
If SET XACT_ABORT is SET to OFF, the linked server must also support nested transactions to update them. When a session already has an existing transaction, if the Provider supports calling ITransactionLocal: StartTransaction, nested transactions are supported. This allows SQL Server to roll back a single statement in a distributed query, rather than roll back the entire transaction.
The preceding rules mean that the following restrictions of the provider do not support nested transactions: update operations are only allowed in distributed transactions when the XACT_ABORT option is set to ON.