Determining the Commit Status for SAP ASE Transactions

If the distributed transaction branch you want to commit or roll back is coordinated by SAP ASE, you can use sp_transactions to determine the commit status of the distributed transaction.

Note: 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"
```