Microsoft KB Archive/315823

= How To Execute Distributed Transaction Using ODBC API with SQL Server =

Article ID: 315823

Article Last Modified on 2/23/2007

-

APPLIES TO


 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft ODBC Driver for SQL Server 2000 2000.80.194
 * Microsoft ODBC Driver for SQL Server 2000 2000.80.380
 * Microsoft ODBC Driver for SQL Server 2000 2000.81.7713.0
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7

-



This article was previously published under Q315823



SUMMARY
When you use Open Database Connectivity (ODBC) application programming interface (API) with Microsoft Distributed Transaction Coordinator (MS DTC), you can execute distributed transactions over multiple databases. This article demonstrates how to execute a distributed transaction over Microsoft SQL Server by using ODBC API and MS DTC from the client side.

Note that distributed transactions can also be executed from the server side through a stored procedure when you call the BEGIN DISTRIBUTED TRANSACTION Transact-SQL statement.



MORE INFORMATION
You can control local transactions at the ODBC API level if you set the connection attribute SQL_ATTR_AUTOCOMMIT to SQL_AUTOCOMMIT_OFF, and then you call the ODBC SQLEndTran function to commit or to roll back each transaction. Do not use these functions to manage a distributed transaction in an ODBC application. Use the following MS DTC COM method instead:
 * 1) Call DtcGetTransactionManager to connect to MS DTC.
 * 2) Call ITransactionDispenser::BeginTransaction to start the distributed transaction and get a transaction object.
 * 3) For each ODBC connection that participates 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.
 * 4) When the transaction is completed, instead of calling the ODBC SQLEndTran function, call the ITransaction::Commit or ITransaction::Rollback methods on the transaction object that is obtained from ITransactionDispenser::BeginTransaction.

The following is a Win32 Console example. Add xoleHlp.Lib in your project reference to properly compile and link the example. To add xoleHlp.Lib, follow these steps:
 * 1) Click Projects, and then click Settings.
 * 2) Click the Link tab.
 * 3) Add xoleHlp.Lib to your Project Reference.

NOTE:SQL Server and MS DTC must be running on all clients and servers. This example does not perform error handling. /* Assume the following TestDistributedTransaction table exists on both the servers. Create this table before running this sample.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestDistributedTransaction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TestDistributedTransaction] GO

CREATE TABLE [dbo].[TestDistributedTransaction] (   [Col1] [int] NULL,    [Col2] [varchar] (100) NULL ) ON [PRIMARY] GO


 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 
 * 6) include 
 * 7) include 
 * 8) include 
 * 9) include 

void main {   HRESULT hr; ITransactionDispenser *pTransactionDispenser = NULL; ITransaction * pITransaction; SQLHDBC hdbc = NULL; SQLHENV    henv; SQLHDBC    hdbc1; SQLHDBC    hdbc2; SQLHSTMT   hstmt1; SQLHSTMT   hstmt2; SQLRETURN  sr;

//SQLRETURN hr; SQLCHAR*   theDiagState = new SQLCHAR[50]; SQLINTEGER theNativeState; SQLCHAR*   theMessageText  = new SQLCHAR[255]; SQLSMALLINT iOutputNo; short      iStringLength2; char       sOutConnectionString[1000];

//CoInitialize call is not needed for DtcGetTransactionManager call, but if you are using the other //OLE object, you need to call it first. //hr = CoInitialize( NULL );

// Allocate two different hdbc handles. We will connect to two different computers that are running SQL Server. hr = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv); hr = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0); hr = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc1); hr = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc2);

//Get the Distributed Transaction Coordinator Manager. hr = DtcGetTransactionManager(NULL, NULL,           IID_ITransactionDispenser, 0, 0, NULL,            (void**) &pTransactionDispenser); //Connecting to first server. hr = SQLDriverConnect(hdbc1,NULL,(SQLCHAR*)&quot;DSN=YourDSN#1;uid=USERID;pwd=PASSWORD&quot; ,SQL_NTS, (SQLCHAR*) sOutConnectionString,100,&iStringLength2,SQL_DRIVER_NOPROMPT); if (hr != SQL_SUCCESS) {       SQLGetDiagRec(SQL_HANDLE_DBC,hdbc1,1,(SQLCHAR*)theDiagState,&theNativeState,(SQLCHAR*)theMessageText,100,&iOutputNo); }

//Connecting to second server. hr = SQLDriverConnect(hdbc2,NULL,(SQLCHAR*) &quot;DSN=YourDSN#2;uid=UID;pwd=PASSWORD&quot; ,SQL_NTS,(SQLCHAR*) sOutConnectionString,100,&iStringLength2,SQL_DRIVER_NOPROMPT); if (hr != SQL_SUCCESS) {       SQLGetDiagRec(SQL_HANDLE_DBC,hdbc2,1,(SQLCHAR*)theDiagState,&theNativeState,(SQLCHAR*)theMessageText,100,&iOutputNo); }

//Start the transaction on DTC. hr = pTransactionDispenser->BeginTransaction (           NULL,       // Controlling IUnknown            ISOLATIONLEVEL_READCOMMITTED,   // Isolation level            0,              // Isolation Flags            NULL,           // Reserved            &pITransaction);    // Ptr to the Transaction Object

//Enlist the two connections (hdbc) in DTC. //You need to do this AFTER you make the connection (for example, after you call SQLDriverConnect or SQLConnect). hr = SQLSetConnectAttr(hdbc1,            SQL_COPT_SS_ENLIST_IN_DTC,                 (SQLPOINTER)pITransaction,SQL_IS_INTEGER);

if (hr != SQL_SUCCESS) {           SQLGetDiagRec(SQL_HANDLE_DBC,hdbc1,1,(SQLCHAR*)theDiagState,&theNativeState,(SQLCHAR*)theMessageText,100,&iOutputNo); printf( &quot;Error :%s\n&quot;, theMessageText); }   hr =SQLSetConnectAttr(hdbc2,             SQL_COPT_SS_ENLIST_IN_DTC,                 (SQLPOINTER)pITransaction,SQL_IS_INTEGER);

if (hr != SQL_SUCCESS) {           SQLGetDiagRec(SQL_HANDLE_DBC,hdbc2,1,(SQLCHAR*)theDiagState,&theNativeState,(SQLCHAR*)theMessageText,100,&iOutputNo); printf( &quot;Error :%s\n&quot;, theMessageText); }

//Allocate statement handle and run a query. hr = SQLAllocHandle(SQL_HANDLE_STMT,hdbc1,&hstmt1); hr = SQLAllocHandle(SQL_HANDLE_STMT,hdbc2,&hstmt2);

hr = SQLExecDirect(hstmt1, (SQLCHAR*) &quot;Insert Into TestDistributedTransaction (Col1, Col2) Values (1,'Test 1')&quot;, SQL_NTS); if (hr != SQL_SUCCESS) {       SQLGetDiagRec(SQL_HANDLE_STMT,hstmt1,1,(SQLCHAR*)theDiagState,&theNativeState,(SQLCHAR*)theMessageText,100,&iOutputNo); printf( &quot;Error :%s\n&quot;, theMessageText); pITransaction->Abort(NULL,FALSE,FALSE); }   else {

//Allocate and run the second query in the second server

hr = SQLExecDirect(hstmt2, (SQLCHAR*) &quot;Insert Into TestDistributedTransaction (Col1, Col2) Values (1,'Test 2')&quot;, SQL_NTS); if (hr != SQL_SUCCESS) {           SQLGetDiagRec(SQL_HANDLE_STMT,hstmt2,1,(SQLCHAR*)theDiagState,&theNativeState,(SQLCHAR*)theMessageText,100,&iOutputNo); pITransaction->Abort(NULL,FALSE,FALSE); }       else {           hr = pITransaction->Commit( 0, XACTTC_SYNC_PHASEONE, 0 ); }

}   pITransaction->Release; hr = SQLFreeHandle(SQL_HANDLE_STMT,hstmt1); hr = SQLDisconnect(hdbc1); hr = SQLFreeHandle(SQL_HANDLE_STMT,hstmt2); hr = SQLDisconnect(hdbc2); hr = SQLFreeHandle(SQL_HANDLE_DBC,hdbc1); hr = SQLFreeHandle(SQL_HANDLE_DBC,hdbc2); hr = SQLFreeHandle(SQL_HANDLE_ENV,henv); //CoUninitialize; }

Keywords: kbhowto KB315823

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.