distributed queries and distributed transactions

Distributed Microsoft®sql Server™ allows you to create a link to an OLE DB data source called a linked server. After you link to an OLE DB data source, you can:

References a rowset from an OLE DB data source as a table in a Transact-SQL statement.


Passes the command to the OLE DB data source and contains the result rowset as a table in a Transact-SQL statement. Each distributed query can reference multiple linked servers, and can perform updates or read operations on each linked server separately. A single distributed query can perform read operations on some linked servers and perform update operations on other linked servers. Typically, Microsoft SQL server requires that the corresponding OLE DB provider support distributed transactions whenever a transaction might update data in multiple linked servers. Therefore, the type of query supported on the linked server depends on the level of support for the transaction in the OLE DB provider. OLE DB defines two optional interfaces for transaction management:

ITransactionLocal supports local transactions in OLE DB data sources.


ITransactionJoin allows a provider to join a distributed transaction that contains other resource managers.
ITransactionLocal is also supported for all providers that support ITransactionJoin.

If a distributed query is executed when the connection is autocommit mode, the following rules apply:

Only read operations are allowed for providers that do not support itransactionlocal.


Allows all update operations to be performed for providers that support ITransactionLocal.
The master SQL server automatically invokes the itransactionlocal in each server that participates in the update operation to start the local transaction and roll back when the statement executes successfully or when the statement fails.

If the distributed query is for distributed partitioned views or when the connection is to an explicit or implicit transaction, the following rules apply:

Only read operations are allowed for providers that do not support ITransactionJoin. Providers that do not support any transaction or support only itransactionlocal cannot participate in the update operation.


If the set XACT_ABORT is set to ON, any providers that support ITransactionJoin are allowed to perform all update operations. The master SQL server automatically invokes the ITransactionJoin in each linked server that participates in the update operation to enlist the server in a distributed transaction. Then MS DTC commits or rolls back when the master server indicates that the transaction is to be committed or rolled back.


If the set XACT_ABORT is set to OFF, the linked server must also support nested transactions in order to perform an update operation on it. Nested transactions are supported when a session already has an existing transaction, and if the provider supports calling ITransactionLocal::StartTransaction. This allows SQL Server to roll back a single statement in a distributed query instead of rolling back the entire transaction.
The above rules imply that nested transactions are not supported by the following restrictions on the provider: update operations are allowed only in distributed transactions when the XACT_ABORT option is set to ON.