EJB3 - using 2 persistence units within a transaction (Exception: Local transaction already has 1 non-XA Resource)

I am trying to use 2 persistence units within the same transaction in a Java EE application deployed on Glassfish.

The 2 persistence units are defined in persistence.xml, as follows:

<persistence-unit name="BeachWater">
<jta-data-source>jdbc/BeachWater</jta-data-source>
...
<persistence-unit name="LIMS">
<jta-data-source>jdbc/BeachWaterLIMS</jta-data-source>
...

These persistence units correspond to JDBC resources and connection pools which I had defined in Glassfish as follows (include one here as both are identical apart from names & database connection info):

JDBC Resource:
JNDI Name: jdbc/BeachWaterLIMS
Pool Name: BEACHWATER_LIMS

Connection Pool:
Name: BEACHWATER_LIMS
Datasource Classname: com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource
Resource Type: javax.sql.ConnectionPoolDataSource

There are 3 stateless session beans, LimsServiceBean, AnalysisServiceBean and AnalysisDataTransformationServiceBean.

Here are the relevant snippets from LimsServiceBean:

@PersistenceContext(unitName = "LIMS")
EntityManager em;
...
public ArrayList<Sample> getLatestLIMSData() {
    Query q = em.createNamedQuery("Sample.findBySubTypeStatus");
    return new ArrayList<Sample>(q.getResultList());
}

From AnalysisServiceBean:

@PersistenceContext(unitName = "BeachWater")
EntityManager em;
...
public ArrayList<AnalysisType> getAllAnalysisTypes() {
    Query q = em.createNamedQuery("AnalysisType.findAll");
    return new ArrayList<AnalysisType>(q.getResultList());
}

And from AnalysisDataTransformationServiceBean:

@EJB
private AnalysisService analysisService;

@EJB
private LimsService limsService;

public void transformData() {
    List<AnalysisType> analysisTypes = analysisService.getAllAnalysisTypes();
    ArrayList<Sample> samples = limsService.getLatestLIMSData();

This ca开发者_StackOverflow中文版ll to limsService.getLatestLIMSData() caused the following exception:

     [exec] Caused by: javax.ejb.TransactionRolledbackLocalException: Exception thrown from bean; nested exception is: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b60e-fcs (12/23/2008))): oracle.toplink.essentials.exceptions.DatabaseException
 [exec] Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: java.lang.IllegalStateException: Local transaction already has 1 non-XA Resource: cannot add more resources.

Having consulted this page, http://msdn.microsoft.com/en-us/library/ms378484.aspx (among many others), I tried changing the definition of the connection pools to:

Connection Pool:
Name: BEACHWATER_LIMS
Datasource Classname: com.microsoft.sqlserver.jdbc.SQLServerXADataSource
Resource Type: javax.sql.XADataSource

Ping via the Glassfish admin console succeeds, but call to analysisService.getAllAnalysisTypes() now throws an exception:

Caused by: javax.ejb.TransactionRolledbackLocalException: Exception thrown from bean; nested exception is: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b60e-fcs (12/23/2008))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: javax.transaction.SystemException

The resource manager is doing work outside a global transaction 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'."

Any ideas?

Change configuration of connection pool in Glassfish:

Connection Pool:
Name: BEACHWATER_LIMS
Datasource Classname: com.microsoft.sqlserver.jdbc.SQLServerXADataSource
Resource Type: javax.sql.XADataSource

Follow the steps on Senthil Balakrishnan's blog, "How to make MSSQL Server XA Datasource Work?" here, http://www.senthilb.com/2010/01/how-to-make-xa-datasource-work-in-mssql.html.

Restart Glassfish.

In order to use two persistence units (and thus two datasources) within a transaction, you need indeed to use XA connections and to configure your pools accordingly (at least one of them, GlassFish supports the last agent optimization that allows to enlist one non XA resource, see http://docs.sun.com/app/docs/doc/820-7695/beanm?a=view). That was for the first error.

For the second error, it seems hard to say anything with the current level of details. Could you provide the stack trace (activate finer logging if required)?