If the distributed transaction branch you want to commit or roll back is coordinated by Adaptive Server, you can use sp_transactions to determine the commit status of the distributed transaction. To do so, complete the following steps.
These steps cannot be used with distributed transactions
that are coordinated by the X/Open XA protocol, MSDTC,
or SYB2PC.
In the server that is executing the transaction branch you want to complete, use sp_transactions with the xid keyword to display information about the transaction. Record the commit node and gtrid of the transaction. For example:
sp_transactions "xid", "00000b1700040000dd6821390001-aa01f04ebb9a-00000b1700040000dd6821390001-aa01f04ebb9a-caserv1-caserv1-0002"
xactkey type coordinator starttime state connection dbid spid loid failover srvname namelen xactname commit_node parent_node gtrid ------------------------------ ----------- ----------- -------------------- ----------------- ---------- ------ ------ ----------- -------------------------- ------------------------------ ------- ----------------------------------------------------------------- ------------- ------------- ------------- 0x00000b2500080000dd6821960001 External ASTC Jun 1 1999 3:47PM Begun Attached 1 8 139 Resident Tx NULL 108 00000b1700040000dd6821390001-aa01f04ebb9a-00000b1700040000dd6821390001-aa01f04ebb9a-caserv1-caserv1-0002 caserv1 sfserv 00000b1700040000dd6821390001-aa01f04ebb9a
In this example, the commit node for the distributed transaction is “caserv1” and the gtrid is “00000b1700040000dd6821390001-aa01f04ebb9a”.
Log on to the server indicated by the commit node. For example:
isql -Usa -Psa_password -Scaserv1
Use sp_transactions with the gtrid keyword to determine the commit status of the distributed transaction having the gtrid obtained in step 1:
sp_transactions "gtrid", "00000b1700040000dd6821390001-aa01f04ebb9a"
xactkey type coordinator starttime state connection dbid spid loid failover srvname namelen xactname commit_node parent_node ------------------------------ ----------- ----------- -------------------- ----------------- ---------- ------ ------ ----------- -------------------------- ------------------------------ ------- ----------------------------------------------------------------- ------------- ------------- 0x00000b1700040000dd6821390001 Local None Jun 1 1999 3:47PM Committed Attached 1 1 2 Resident Tx NULL 17 $user_transaction caserv1 caserv1
In this example, the local transaction with the specified gtrid has committed, as indicated by the “state” column. The System Administrator should heuristically commit the prepared transaction examined in step 1.
Using an account with System Administrator privileges, log on to the server that is executing the transaction branch you want to complete:
isql -Usa -Psa_password -Ssfserv
Use dbcc complete_xact to commit the transaction. In this example, the System Administrator should use the commit keyword to maintain consistency with the distributed transaction:
dbcc complete_xact "00000b1700040000dd6821390001-aa01f04ebb9a-00000b1700040000dd6821390001-aa01f04ebb9a-caserv1-caserv1-0002", "commit"
