BEA Aqualogic DSP

I tried to do a federated update using Aqualogic DSP.  I got this error message:

java.rmi.RemoteException: EJB Exception: ; nested exception is: com.bea.ld.dsmediator.DataServiceException: java.sql.SQLException: Connection has already been created in this tx context for pool named inventoryMM. Illegal attempt to create connection from another pool: inventoryDev2

This issue appears to be that the JDBC driver that I was using is not XA compliant.  I tried switching to the XA version of the BEA MS SQL Server JDBC driver, but it required changes to the underlying SQL Servers to function correctly.  So I tried the jTDS XA driver instead, in 'XA emulation' mode where it pretends to participate in XA transactions, but doesn't.  That didn't work -- I got the error message:

Error obtaining database connection 'inventoryDev1': SQL operations are not allowed with no global transaction by default for XA drivers. If the XA driver supports performing SQL operations with no global transaction, explicitly allow it by setting "SupportsLocalTransaction" JDBC connection pool property to true. In this case, also remember to complete the local transaction before using the connection again for global transaction, else a XAER_OUTSIDE XAException may result. To complete a local transaction, you can either set auto commit to true or call Connection.commit() or Connection.rollback().

I tried the 'supportsLocalTransaction' suggestion, but then I got another error message (which I forgot to write down).  [After trying the steps that follow, I wonder whether it all would have worked if I had just restarted the servers in the right order...]

I decided to bite the bullet and use BEA's XA drivers.  The first step is to install a DLL and some stored procedures in each server, following the instructions in Installing Stored Procedures for JTA from the WebLogic JDBC driver manual.  I then created drivers for three machines.  Two were Windows 2003 Server boxes and the connection pools came up right away.  However, the third machine was my XP workstation, and when I tried to test the connection pool, I got a message saying the SQL Server was unable to contact the DTC service.  I tried to start the service, but I still got the error message:

java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]xa_open (0) returns -3

After much fiddling about, I discovered that I had to restart all the relevant services after making changes.

I tried to run some data service functions in WebLogic Workshop, but once again I got the "no global transaction" error.  So once again I tried setting the 'supportsLocalTransaction' option, but this time the data service worked... as did a single-source read through the SDO client, and a single-source write, and a multi-source write (which was the problem to be solved).

  So, the winning formula was:

1. Install the required DLL and stored procedures in SQL Server (Installing Stored Procedures for JTA).
2. Start the DTC service.
3. Use Component Services administrative tool to enable remote logins and XA transactions in the DTC service, and to start the service.
4. Restart SQL Server.
5. Start the WebLogic server.
6. Create the connection pool in WebLogic using the BEA XA SQL Server driver.
7. Set the 'support local transactions' option on the pool.
8. Repeat steps 6 and 7 as many times as needed.

A posting on the BEA JDBC newsgroup was helpful, SQLException when creating XA connection pool.