KB-1010 "Could not find stored procedure 'master..xp_sqljdbc_xa_recover'" error thrown intermittently in application server logs

Symptoms

During JBoss startup, the following warning is shown (after startup this warning will be shown intermittently):

21:39:25,801 WARN  [com.arjuna.ats.jta] (Periodic Recovery) ARJUNA016027: Local XARecoveryModule.xaRecovery got XA exception XAException.XAER_RMERR: javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Could not find stored procedure 'master..xp_sqljdbc_xa_recover'.
    at com.microsoft.sqlserver.jdbc.SQLServerXAResource.DTC_XA_Interface(SQLServerXAResource.java:647)
    at com.microsoft.sqlserver.jdbc.SQLServerXAResource.recover(SQLServerXAResource.java:728)
    at org.jboss.jca.adapters.jdbc.xa.XAManagedConnection.recover(XAManagedConnection.java:362)
    at org.jboss.jca.core.tx.jbossts.XAResourceWrapperImpl.recover(XAResourceWrapperImpl.java:185)
    at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.xaRecoveryFirstPass(XARecoveryModule.java:541) [jbossjts-jacorb-4.17.29.Final-redhat-1.jar:4.17.29.Final-redhat-1]
    at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:181) [jbossjts-jacorb-4.17.29.Final-redhat-1.jar:4.17.29.Final-redhat-1]
    at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:747) [jbossjts-jacorb-4.17.29.Final-redhat-1.jar:4.17.29.Final-redhat-1]
    at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:375) [jbossjts-jacorb-4.17.29.Final-redhat-1.jar:4.17.29.Final-redhat-1]

Cause

This warning arises because Microsoft SQL Server is configured as an XA data source in appian-ds.xml, but SQL Server does not come configured for XA transactions by default.

For more information, refer to Redhat's tech note (XAER_RMERR during XA transaction recovery for Oracle database in JBoss EAP) on the issue.

Action

This warning does not prevent Appian from working with SQL Server. If desired, follow the below steps to configure SQL Server for XA transactions.

1. Download the latest driver from the Microsoft SQL Server JDBC Drivers page.
2. Extract the content of the driver in a temporary directory, such as the Desktop. An application such as 7-zip for Windows is able to extract archives ending in .tar.gz.
3. Inside the unzipped content from step 2, navigate to the sqljdbc_4.0\enu\xa directory.
4. In the directory exists a file named xa_install.sql with instructions on how to enable this feature in SQL Server. These steps have been outlined below:
   1. Identify if the installed version of SQL Server is 32-bit or 64-bit. This can usually be identified by running SELECT @@VERSION; in SQL Server Management Studio. The output will either mention X86 (32-bit) or X64 (64-bit).
   2. Depending on the application's architecture, copy the sqljdbc_xa.dll from the unzipped folder <Microsoft JDBC Driver 4.0 for SQL Server>\sqljdbc_4.0\enu\xa\x64 for 64-bit or <Microsoft JDBC Driver 4.0 for SQL Server download>\sqljdbc_4.0\enu\xa\x86 for 32-bit.
   3. Paste the sqljdbc_xa.dll copied from step 5.2 into <SQL_SERVER_2008_HOME>\Tools\Binn and <SQL_SERVER_2008_HOME>\LocalDB\Binn.
   4. Execute the xa_install.sql script located in <Microsoft JDBC Driver 4.0 for SQL Server download>\sqljdbc_4.0\enu\xa.

In some instances, the following warning message will be displayed:

WARN [loggerI18N] [com.arjuna.ats.internal.jta.recovery.xarecovery1] Local XARecoveryModule.xaRecovery got XA exception javax.transaction.xa.XAException: The function RECfailed. The status is: -3. Error: "*** SQLJDBC_XA DTC_ERROR Context: xa_recover, state=1, StatusCode:-3 (0xFFFFFFFD) ***", XAException.XAER_RMERR

To correct this, perform the following:

1. Open Control Panel > Administrative Tools > Component Services.
2. Open the Distributed Transaction Services item for the necessary computer(s).
3. Right-click the Local DTC option.
4. Select the Security tab.
5. Check Enable XA Transactions, forcing the MS DTC service to restart.
6. Restart SQL Server to make sure it picks up the MS DTC changes.
7. Restart JBoss.

Note: These steps have been tested in Microsoft SQL Server 2008. For detailed instructions for other versions, please consult Microsoft's documentation.

Workaround

As an alternative to configuring SQL Server for XA transactions, follow these steps to hide the warning message instead:

1. Stop JBoss.
2. Open standalone.xml.
3. Search for the element .
4. Under the level element, change the name from WARN to ERROR. This changes the logging level so the warning is functionally hidden.
5. Start JBoss.

Affected Versions

This article applies to all versions of Appian.