[teiid-issues] [JBoss JIRA] Commented: (TEIID-559) MMSQLException querying with SQL Server XA Connector in Teiid Designer

[Steven Hawkins (JIRA)]

    [ https://jira.jboss.org/jira/browse/TEIID-559?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12466342#action_12466342 ] 

Steven Hawkins commented on TEIID-559:
--------------------------------------

Running with arjuna logging and with the pre jdbc 4 MS driver the log showed:  

WARNING: [com.arjuna.ats.internal.jta.transaction.arjunacore.enliststarterror] [com.arjuna.ats.internal.jta.transaction.arjunacore.enliststarterror] TransactionImple.enlistResource - XAResource.start returned: [com.arjuna.ats.jta.utils.unknownerrorcode] Unknown error code:0 for < 131075, 37, 24, 116120110109103114951001131129549455510248484849484958100994955585297485150485053585055102484848494849581009949555852974851504850535852 >
javax.transaction.xa.XAException: java.sql.SQLException: Failed to create the XA control connection. Error: Could not find stored procedure 'master..xp_sqljdbc_xa_init'..
	at com.microsoft.sqlserver.jdbc.SQLServerXAResource.start(Unknown Source)
	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:777)
	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:500)
	at org.teiid.dqp.internal.transaction.TransactionServerImpl.enlist(TransactionServerImpl.java:572)
...

So the server is not setup for XA.  With the MS jdbc 4 driver we still get the enlist error, but the message and stack don't give a clear indication as to what is wrong.

> MMSQLException querying with SQL Server XA Connector in Teiid Designer
> ----------------------------------------------------------------------
>
>                 Key: TEIID-559
>                 URL: https://jira.jboss.org/jira/browse/TEIID-559
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 6.0.0
>         Environment: Teiid Designer Kit:  teiid_designer_6.0.0.v20090427_SNAPSHOT.zip
>            Reporter: Barry LaFond
>            Assignee: Steven Hawkins
>             Fix For: 6.1.0
>
>         Attachments: E2E Update Virtual Groups Test Script.odt, UpdateVirtualGroup_MPS,zip
>
>
> E2E Update through Virtual Groups Test Script was successful with INSERT, UPDATE, DELETE through Oracle connector (Native Driver)
> However updates are failing with SQL Server XA Connector.    
> For reference, standard SQL Server Connector URL =  jdbc:sqlserver://slntdb02.mm.atl2.redhat.com:1433;databasename=customeraccounts
> com.metamatrix.jdbc.MMSQLException: Failed to enlist the XAResource in Transaction.
> 	at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:82)
> 	at com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:63)
> 	at com.metamatrix.jdbc.MMStatement.executeSql(MMStatement.java:424)
> 	at com.metamatrix.jdbc.MMStatement.execute(MMStatement.java:326)
> 	at net.sourceforge.sqlexplorer.sqlpanel.SqlExecProgress.processQuery(SqlExecProgress.java:192)
> 	at net.sourceforge.sqlexplorer.sqlpanel.SqlExecProgress.run(SqlExecProgress.java:121)
> 	at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
> Caused by: [XATransactionException]Failed to enlist the XAResource in Transaction.
> 	at org.teiid.dqp.internal.transaction.TransactionServerImpl.enlist(TransactionServerImpl.java:561)
> 	at sun.reflect.GeneratedMethodAccessor260.invoke(Unknown Source)
> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> 	at java.lang.reflect.Method.invoke(Method.java:616)
> 	at com.metamatrix.common.log.LogManager$1.invoke(LogManager.java:407)
> 	at $Proxy6.enlist(Unknown Source)
> 	at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.enlistResource(SynchConnectorWorkItem.java:118)
> 	at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.createExecution(SynchConnectorWorkItem.java:101)
> 	at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:151)
> 	at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
> 	at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:69)
> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
> 	at java.lang.Thread.run(Thread.java:636)
> To reproduce:
> 1) Install Designer:   teiid_designer_6.0.0.v20090427_SNAPSHOT.zip
> 2) Create SQL XA Connector for CustomerAcccounts and Oracle XA Connectors to Products
> 3) Open UpdatesVDB in VDB Editor
> 4) Select Execute tab and Edit connectors to bind sources to XA Connectors 
> 5) Save VDB
> 6) Select Execute VDB
> 7) In SQL Editor, paste:  DELETE FROM Updates.UpdateAccount where CustID = 'CST02000' and LastName = 'Metamatrix'
> 8) Executing the query should produce above Exception

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira