Enabling XA Transactions in SQL Server 2008 for ADEP Interactive Statements

For Oracle, see this.

The Adobe Customer Experience solution “Customer Communications – Interactive Statements” (formerly Interactive Statements Solution Accelerator) requires an XA-type JDBC data source. XA is a specification for distributed transaction processing that involves multiple resources such as a database, or a JMS queue. More here.

In the JBoss Turnkey as well as in the pre-configured JBoss EAP tree, it is defined in the file adobe-sa-ds.xml in %JBOSS_HOME%\aep_sqlserver\deploy\. The JNDI name is “AdobeDefaultSA_DS“. It uses a different driver class (com.microsoft.sqlserver.jdbc.SQLServerXADataSource) compared to the other ADEP Document Services JDBC data sources (IDP_DS and EDC_DS) which are non-XA (com.microsoft.sqlserver.jdbc.SQLServerDriver). Both these classes are available in version 3 of Microsoft’s SQL Server JDBC Driver which supports SQL Server 2008.

Default SQL Server 2008 installs are not configured for XA transactions. If you have a JBoss 5.1 EAP instance running ADEP Document Services against Microsoft SQL Server 2008, you will get the following error messages every 2 minutes in JBoss server.log:

[com.arjuna.ats.internal.jta.recovery.xarecovery1] Local XARecoveryModule.xaRecovery got XA exception javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: “Could not find stored procedure ‘master..xp_sqljdbc_xa_init_ex’.”, XAException.XAER_RMERR

To configure a SQL Server 2008 instance for XA, your SQL Server 2008 DBA has to perform several steps server-side, on the SQL Server machine:

1) Download the Microsoft SQL Server JDBC Driver 3.0 from here and install it somewhere (run the .exe)

2) Copy sqljdbc_xa.dll from %JDBC_DRIVER_INSTALL_ROOT%\sqljdbc_3.0\enu\xa\x64\ (assuming the SQL Server 2008 instance is 64-bit) to %SQLSERVER_INSTALL_ROOT%\Instance_root\MSSQL10.MSSQLSERVER\MSSQL\Binn\


3) Copy-paste the contents of the script xa_install.sql in %JDBC_DRIVER_INSTALL_ROOT%\sqljdbc_3.0\enu\xa\ and execute it using SQL Server Management Studio’s Query Editor.

4) Confirm the creation of new stored procedures in SQL Server Management Studio. In the left pane, navigate to Programmability->Extended Stored Procedures for the “master” database – it should look something like this:


5) Using SQL Server Management Studio’s Query Editor, assign the ‘SqlJDBCXAUser’ role to the SQL Server user that the JDBC connection pool uses for authentication and execute it. The following script assigns the role to the user ADEPJBoss:

USE master
EXEC sp_grantdbaccess 'ADEPJBoss', 'ADEPJBoss'
EXEC sp_addrolemember [SqlJDBCXAUser], 'ADEPJBoss'

“Command(s) completed successfully.”


6) Choose the Windows menu Administrative Tools->Component Services.

7) In the left navigation pane, navigate to Console Root->Component Services->Computers->My Computer->Distributed Transaction Coordinator->Local DTC.

8) Highlight ‘Local DTC’, right-click the mouse and choose ‘Properties’.

9) Choose the ‘Security’ tab. Ensure that the checkbox for ‘Enable XA Transactions’ is checked. Please see screenshot below.

10) Click Apply, then OK.


11) Choose the Windows menu Administrative Tools->Services. In it, make sure that the “Distributed Transaction Coordinator” service is set to start automatically. Start it.

12) Also make sure that the other services it depends on, namely ‘Remote Procedure Call‘, ‘Security Accounts Manager‘ are also set to start automatically.

13) Re-start the SQL Server instance

14) Re-start the appserver instance hosting ADEP Document Services and make sure that the error messages no longer appear.

VN:F [1.9.22_1171]
Was this helpful? Please rate the content.
Rating: 9.5/10 (32 votes cast)
Enabling XA Transactions in SQL Server 2008 for ADEP Interactive Statements, 9.5 out of 10 based on 32 ratings
This entry was posted in Document Services, Customer Experience Solutions, ADEP and tagged , , , , , , . Bookmark the permalink.

One Response to Enabling XA Transactions in SQL Server 2008 for ADEP Interactive Statements

  1. Chalukya says:

    Thank Jayan, finally was able to install ADEP successfully …

    Platform: Windows 2000 Server x64, JBOss, SQL Server 2008.