Microsoft®sql Server™ allows you to create a link to an OLE DB data source called a linked server. After linking to the 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 the Transact-SQL statement.
Each distributed query can reference multiple linked servers, and each linked server can perform an update or read operation, respectively. 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 appropriate OLE DB provider support distributed transactions whenever a transaction may update data from 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:
The itransactionlocal supports local transactions in OLE DB data sources.
ITransactionJoin allows a provider to join a distributed transaction that contains other resource managers.
All providers that support ITransactionJoin also support ITransactionLocal.
If a distributed query is executed when the connection is autocommit mode, the following rules apply:
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 invokes itransactionlocal on each linked server that participates in the update operation to initiate a local transaction and to commit when the statement executes successfully or to roll back if the statement execution fails.
If the distributed query is for a distributed partitioned view or if it is executed when the connection is an explicit or implicit transaction, the following rules apply:
For providers that do not support ITransactionJoin, only read operations are allowed. Providers that do not support any transactions or support only itransactionlocal cannot participate in the update operation.
If set XACT_ABORT is on, any provider that supports ITransactionJoin allows all update operations to be performed. The master SQL server automatically invokes ITransactionJoin in each linked server that participates in the update operation to enlist the server in a distributed transaction. MS DTC then submits or rolls back when the master server indicates that the transaction is to be committed or rolled back.
If set XACT_ABORT is 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 enables SQL Server to roll back a single statement in a distributed query instead of rolling back the entire transaction.
The above rule means that the following restrictions on the provider do not support nested transactions: Only the XACT_ABORT option is set to ON, and the update operation is allowed in the distributed transaction.
http://www.bkjia.com/PHPjc/631194.html www.bkjia.com true http://www.bkjia.com/PHPjc/631194.html techarticle Microsoftreg; SQL server#8482; Allows you to create a link to an OLE DB data source called a linked server. After linking to an OLE DB data source, you can: reference a rowset from an OLE DB data source as ...
