Microsoft? SQL Server? Allows the creation of links to OLE DB data sources called link servers. After linking to the OLE DB data source, you can:
From the OLE DB data source reference routine, the table in the Transact-SQL statement.
Pass the command to the OLE DB data source and contain the result rowset as a table in the Transact-SQL statement.
Each distributed query can reference multiple links of servers, and can perform updates or read operations separately for each link. Single distributed query
You can perform read operations for some links, and execute update operations for other links. Normally, whenever a transaction may be more
When data in a link server, Microsoft SQL Server requires the corresponding OLE DB provider to support distributed transactions. Therefore, the link server
The supported query type depends on the support level of the transaction in the OLE DB provider. OLE DB defines two optional interfaces for transaction management:
ItraSactionLocal supports local transactions in OLE DB data sources.
ItransactionJoin allows providing program linkages to include distributed transactions of other resource managers.
All provisions that support iTransactionJoin also support ITRANSACTIONLOCAL.
If a distributed query is performed when the connection is automatically submit mode, the following rules apply:
For providers that do not support iTransactionLocal, only read operations are allowed.
For providers that support iTransactionLocal, all updates are allowed.
The master SQL Server automatically calls ITRANSACTIONLOCAL in the server participating in the update operation to start the local transaction and in the statement
Spend in successful running or rollback when the statement does fail.
Apply the following rules if the distributed query is performed for a distributed partition view or when it is connected to explicit or hidden transactions:
For providers that do not support iTransactionJoin, only the read operation is permitted. Do not support any transactions or only support ITRANSACTIONLOCAL
The program cannot participate in the update operation.
If set XACT_ABORT is set to ON, all updates are allowed for any provider that supports ITRANSActionJoin. Host
SQL Server automatically invokes the ITransactionJoin in the link server participating in the update operation to register the server in a distributed transaction. Course
The MS DTC will submit or roll back when the master server indicates that the transaction is to be submitted or rolled.
If set XACT_ABORT is set to OFF, the link server must also support nested transactions to perform update operations. When the session already has an existing
When the transaction is supported, the nested transaction is supported if the provider supports the ITRANSACTIONLOCAL :: StartTransaction. This makes SQL Server roll back
Single statements in distributed queries, not rolling throughout the transaction.
The above rules mean that the following restrictions are not supported nested transactions: Updated operations are allowed only when the XACT_ABORT option is set to ON.
Related resources: .NET briefing, distributed transaction processing
