Share via

Issues with SQLSetConnectAttr and SQL_ATTR_ENLIST_IN_XA

Kristian Ivarsson 0 Reputation points
2026-03-26T21:52:19.2733333+00:00

When using this for implementing an xa-switch for usage with an XATMI product with 2 different thread of controls, the ODBC-driver returns XAER_RMERR in the following scenario

Thread A OP_START with TMNOFLAGS
Thread A OP_END with TMSUSPEND
Thread B OP_START with TMJOIN
Thread B OP_END with TMSUCCESS
Thread A OP_START with TMRESUME -> SQLSetConnectAttr return -6 and sets XACALLPARAM::status to -6 (XAER_PROTO) as well

Due to some ODBC debugging and searching I found out that the "error" was that the driver claimed it was an error to TMRESUME a non TMSUSPENDed control. This association (made in thread A) is suspended though and according to the XA specification this should be allowed. When playing around trying to be an XATMI-product and invoked the OP_END in thread B with TMSUSPEND it worked, but we're not in the position where we can manipulate how the XATMI-products work. The scenario described above works for Oracle DB and their ODBC driver. I'm using ODBC Driver 18 for SQL Server on WSL/Ubuntu LTS 24.04.

In reality, thread A and B have 2 separate HDBC connections and they are even two separate operating system processes.

No XID branching occurs (of cource, because then there wouldn't be a TMJOIN)

Does anyone have a workaround for this (or should Microsoft fix this) ?

It is also weird and cumbersome that SQLSetConnectAttr returns -6 instead of SQL_ERROR (or any of the error codes it should return according to the ODBC-specification) and the possibility to use SQLGetDiagRec() in a proper way or maybe just SQL_SUCCESS and just let the error be in XACALLPARAM::status (as the documentation says)

SQL Server Database Engine
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Kristian Ivarsson 0 Reputation points
    2026-03-26T22:03:39.49+00:00

    About "SQLSetConnectAttr itself returns the usual ODBC return codes, and diagnostics are available via the standard ODBC mechanism.", that is clearly not the case and in this scenario it returned -6, by fact, but even though this -6 is irritating, it is just a sidenote to the read show-stopper that makes this XA-mechanism not working in a distributed scenario (the thing that XA is particularly meant to solve)

    0 comments
  2. Q&A Assist
    2026-03-26T21:52:47.8066667+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    SQLSetConnectAttr with SQL_COPT_SS_ENLIST_IN_XA is a SQL Server–specific extension, and the behavior is defined by the SQL Server ODBC driver rather than the generic ODBC spec.

    From the SQL Server Native Client/ODBC documentation:

    • XA enlistment is done via SQLSetConnectAttr(hdbc, SQL_COPT_SS_ENLIST_IN_XA, param, SQL_IS_POINTER); where param is an XACALLPARAM*.
    • The XA call result is returned in XACALLPARAM::status; SQLSetConnectAttr itself returns the usual ODBC return codes, and diagnostics are available via the standard ODBC mechanism.
    • XA enlistment (SQL_COPT_SS_ENLIST_IN_XA) is only valid after the connection is established.

    Relevant pieces from the docs:

    • XACALLPARAM::status is explicitly defined as the “Return status from the TM” and is where XA error codes such as XAER_RMERR are surfaced.
    • SQLSetConnectAttr diagnostics list only standard SQLSTATEs (08S01, 24000, 25000, HY000, etc.) and do not include XA-specific codes. XA-specific errors are therefore expected to be reported via XACALLPARAM::status, not via SQLSTATE.
    • For SQL Server–specific connection attributes, the driver may require that the attribute be set before or after connection, or ignore it if already set. For XA enlistment, the attribute is explicitly “After” connection (SQL_COPT_SS_ENLIST_IN_XA | After).

    Given this, two important consequences follow for the scenario described:

    1. XA error reporting path
      XA-specific failures (such as the driver’s interpretation of TMRESUME rules) are surfaced through XACALLPARAM::status, not through SQLSTATE. The documentation states that SQLSetConnectAttr returns the usual ODBC success/error codes and that XA-specific details are in the XA structure. This matches what is observed: XACALLPARAM::status = XAER_RMERR.
    2. Driver-specific XA semantics
      The SQL Server ODBC driver enforces its own rules for XA enlistment and transaction state transitions. The generic ODBC documentation only defines the SQLSTATE 25000 for illegal operations while in a local or DTC transaction (for example, trying to enlist in DTC while in a local transaction, or starting a local transaction after DTC enlistment). It does not define how TMJOIN, TMSUSPEND, or TMRESUME must behave across multiple HDBC handles or processes; that behavior is left to the XA implementation and is not standardized in the ODBC layer.

    Because of that, the behavior difference between Oracle’s driver and the SQL Server ODBC driver is an implementation difference in the XA resource manager, not something that can be corrected or worked around purely via ODBC calls. The SQL Server documentation does not describe a supported way to override or relax the driver’s XA state checks.

    From the available documentation, the only supported patterns are:

    • Use SQL_COPT_SS_ENLIST_IN_XA only after connection is established.
    • Treat XACALLPARAM::status as the authoritative XA result for each call.
    • Respect the driver’s XA state machine; there is no documented mechanism to force a TMRESUME that the driver considers invalid.

    No documented workaround exists in the provided material for changing how the SQL Server ODBC driver validates TMRESUME versus TMSUSPEND across different HDBC handles or processes. The behavior observed (XA error in XACALLPARAM::status, with SQLSetConnectAttr returning a non-standard code for XA) is consistent with the documented pattern that XA-specific errors are reported through the XA structure rather than standard ODBC SQLSTATEs.

    Given the constraints in the documentation, the only supported path is to align the XA usage pattern with what the SQL Server driver accepts (for example, ensuring the suspend/resume pairing matches the driver’s expectations on the same logical association), or to request a change/fix from Microsoft through a support channel, since altering the XA state rules is not exposed via ODBC APIs.

    References:

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.