MS DTC Distributed Transactions
Applications written using OLE DB, ODBC, 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, however, also contain support at the API level for managing distributed transactions. OLE DB and ODBC applications can use these API functions to manage distributed transactions that include other COM resource managers that support MS DTC transactions other than Microsoft® SQL Server™. They can also use the API functions to gain more control over the boundaries of a distributed transaction that includes several SQL Servers.
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.
- The consumer then calls the ITransaction::Commit or ITransaction::Rollback methods on the transaction object obtained by ITransactionLocal::StartTransaction.
To control a distributed transaction, the consumer instead:
- 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.