MS DTC Distributed Transactions
Applications written using OLE DB, Open Database Connectivity (ODBC), ActiveX Data Objects (ADO), or DB-Library can use Transact-SQL distributed transactions by issuing Transact-SQL statements to start and stop Transact-SQL distributed transactions. OLE DB and ODBC also contain support at the application programming interface (API) level for managing distributed transactions. OLE DB and ODBC applications can use these API functions to manage distributed transactions that include other Component Object Model (COM) resource managers that support Microsoft Distributed Transaction Coordinator (MS DTC) transactions other than the SQL Server Database Engine. They can also use the API functions to gain more control over the boundaries of a distributed transaction that includes several computers running instances of the Database Engine.
ODBC Distributed Transactions
You can control local transactions at the ODBC API level by setting the connection attribute SQL_ATTR_AUTOCOMMIT to SQL_AUTOCOMMIT_OFF and then by calling the ODBC SQLEndTran function to commit or roll back each transaction. Do not use these functions to manage a distributed transaction in an ODBC application. Use the MS DTC COM methods instead:
- Call DtcGetTransactionManager to connect to MS DTC.
- Call ITransactionDispenser::BeginTransaction to start the distributed transaction and get a transaction object.
- For each ODBC connection participating in the distributed transaction, call the ODBC function SQLSetConnectAttr with fOption set to SQL_COPT_SS_ENLIST_IN_DTC and vParam holding the address of the transaction object from ITransactionDispenser::BeginTransaction.
- When the transaction is completed, instead of calling the ODBC SQLEndTran function, call the ITransaction::Commit or ITransaction::Rollback methods on the transaction object obtained from ITransactionDispenser::BeginTransaction.
OLE DB Distributed Transactions
The model for controlling a distributed transaction in OLE DB is similar to controlling a local transaction. To control a local transaction, an OLE DB consumer:
- Uses the ITransactionLocal::StartTransaction method to start the local transaction and get a transaction object.
- Calls the ITransaction::Commit or ITransaction::Rollback methods on the transaction object obtained by ITransactionLocal::StartTransaction.
To control a distributed transaction, the consumer:
- Calls DtcGetTransactionManager to connect to MS DTC.
- Calls ITransactionDispenser::BeginTransaction to start the distributed transaction and get a transaction object.
- Calls the ITransactionJoin interface of the distributed transaction object for each connection participating in the distributed transaction.
- Calls the ITransaction::Commit or ITransaction::Rollback methods of the distributed transaction object to complete the transaction.
See Also
Other Resources
Supporting Distributed Transactions
Performing Distributed Transactions