1 Reply Latest reply on Mar 18, 2011 3:31 PM by Joe Weinstein-Oracle

    Sql Exception on Testing Configuration with SQL Server JDBC driver for XA

    838569
      I have a requirement of analyzing the behavior of SQL Server JDBC data sources for XA transactions in our application.We have been using Non-XA drivers for both Oracle and SQL Server as we had no requirement for transactions spanning across multiple databases in past.I have setup and tested the XA driver for Oracle (Oracle Driver (Thin XA) for Instance Connections 9.0.1,9.2.0,10,11) in Weblogic 11g and its working perfectly for transactions spanning across two databases.No when I am trying to configure weblogic 11g R1 for Sql server JDBC driver to support XA transactions with driver details as follows,

      Server:Weblogic 11g R1
      Driver Type: MS Sql Server
      Database Driver :Oracle's MS SQL Server Driver(Type 4 XA) Version:7.0,2000,2005)

      Database:SQL Server 2005(Single Instance)

      and try to create a new data source and select "Test Configuration" and following error is thrown ,

      <Mar 17, 2011 4:49:49 PM GMT+05:30> <Error> <Console> <BEA-240003> <Console encountered the following error java.sql.SQLException: [OWLS][SQLServer JDBC Driver][SQLServer]xa_open (0) returns -3
      at weblogic.jdbc.sqlserverbase.BaseExceptions40.createAppropriateSQLExceptionInstance(Unknown Source)
      at weblogic.jdbc.sqlserverbase.BaseExceptions40.createSQLException(Unknown Source)
      at weblogic.jdbc.sqlserverbase.BaseExceptions.createException(Unknown Source)
      at weblogic.jdbc.sqlserverbase.BaseExceptions.getException(Unknown Source)
      at weblogic.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
      at weblogic.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
      at weblogic.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
      at weblogic.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
      at weblogic.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
      at weblogic.jdbc.sqlserverbase.BaseStatement.commonTransitionToState(Unknown Source)
      at weblogic.jdbc.sqlserverbase.BaseStatement.postImplExecute(Unknown Source)
      at weblogic.jdbc.sqlserverbase.BasePreparedStatement.postImplExecute(Unknown Source)
      at weblogic.jdbc.sqlserverbase.BaseStatement.commonExecute(Unknown Source)
      at weblogic.jdbc.sqlserverbase.BaseStatement.executeUpdateInternal(Unknown Source)
      at weblogic.jdbc.sqlserverbase.BasePreparedStatement.executeUpdate(Unknown Source)
      at weblogic.jdbcx.sqlserver.SQLServerImplXAResource.executeXaRpc(Unknown Source)
      at weblogic.jdbcx.sqlserver.SQLServerImplXAResource.executeXaRpc(Unknown Source)
      at weblogic.jdbcx.sqlserver.SQLServerImplXAResource.open(Unknown Source)
      at weblogic.jdbcx.sqlserverbase.BaseXAConnection.init(Unknown Source)
      at weblogic.jdbcx.sqlserverbase.BaseXAConnection40.init(Unknown Source)
      at weblogic.jdbc.sqlserverbase.BaseClassCreatorForJDBC40.createXaConnection(Unknown Source)
      at weblogic.jdbcx.sqlserverbase.BaseXADataSource.getXAConnection(Unknown Source)
      at com.bea.console.utils.jdbc.JDBCUtils.testConnection(JDBCUtils.java:550)
      at com.bea.console.actions.jdbc.datasources.createjdbcdatasource.CreateJDBCDataSource.testConnectionConfiguration(CreateJDBCDataSource.java:450)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:597)
      at org.apache.beehive.netui.pageflow.FlowController.invokeActionMethod(FlowController.java:870)
      at org.apache.beehive.netui.pageflow.FlowController.getActionMethodForward(FlowController.java:809)
      at org.apache.beehive.netui.pageflow.FlowController.internalExecute(FlowController.java:478)
      at org.apache.beehive.netui.pageflow.PageFlowController.internalExecute(PageFlowController.java:306)
      at org.apache.beehive.netui.pageflow.FlowController.execute(FlowController.java:336)
      at org.apache.beehive.netui.pageflow.internal.FlowControllerAction.execute(FlowControllerAction.java:52)
      at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
      at org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.access$201(PageFlowRequestProcessor.java:97)
      at org.apache.beehive.netui.pageflow.PageFlowRequestProcessor$ActionRunner.execute(PageFlowRequestProcessor.java:2044)
      at org.apache.beehive.netui.pageflow.interceptor.action.internal.ActionInterceptors$WrapActionInterceptorChain.continueChain(ActionInterceptors.java:64)
      at org.apache.beehive.netui.pageflow.interceptor.action.ActionInterceptor.wrapAction(ActionInterceptor.java:184)
      at org.apache.beehive.netui.pageflow.interceptor.action.internal.ActionInterceptors$WrapActionInterceptorChain.invoke(ActionInterceptors.java:50)
      at org.apache.beehive.netui.pageflow.interceptor.action.internal.ActionInterceptors$WrapActionInterceptorChain.continueChain(ActionInterceptors.java:58)
      at org.apache.beehive.netui.pageflow.interceptor.action.internal.ActionInterceptors.wrapAction(ActionInterceptors.java:87)
      at org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.processActionPerform(PageFlowRequestProcessor.java:2116)
      at com.bea.console.internal.ConsolePageFlowRequestProcessor.processActionPerform(ConsolePageFlowRequestProcessor.java:261)
      at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
      at org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.processInternal(PageFlowRequestProcessor.java:556)
      at org.apache.beehive.netui.pageflow.PageFlowRequestProcessor.process(PageFlowRequestProcessor.java:853)
      at org.apache.beehive.netui.pageflow.AutoRegisterActionServlet.process(AutoRegisterActionServlet.java:631)
      at org.apache.beehive.netui.pageflow.PageFlowActionServlet.process(PageFlowActionServlet.java:158)
      at com.bea.console.internal.ConsoleActionServlet.process(ConsoleActionServlet.java:256)
      at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
      at com.bea.console.internal.ConsoleActionServlet.doGet(ConsoleActionServlet.java:133)
      at org.apache.beehive.netui.pageflow.PageFlowUtils.strutsLookup(PageFlowUtils.java:1199)
      at com.bea.portlet.adapter.scopedcontent.ScopedContentCommonSupport.executeAction(ScopedContentCommonSupport.java:686)
      at com.bea.portlet.adapter.scopedcontent.ScopedContentCommonSupport.processActionInternal(ScopedContentCommonSupport.java:142)
      at com.bea.portlet.adapter.scopedcontent.PageFlowStubImpl.processAction(PageFlowStubImpl.java:106)
      at com.bea.portlet.adapter.NetuiActionHandler.raiseScopedAction(NetuiActionHandler.java:111)
      at com.bea.netuix.servlets.controls.content.NetuiContent.raiseScopedAction(NetuiContent.java:181)
      at com.bea.netuix.servlets.controls.content.NetuiContent.raiseScopedAction(NetuiContent.java:167)
      at com.bea.netuix.servlets.controls.content.NetuiContent.handlePostbackData(NetuiContent.java:225)
      at com.bea.netuix.nf.ControlLifecycle$2.visit(ControlLifecycle.java:180)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:324)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walkRecursive(ControlTreeWalker.java:334)
      at com.bea.netuix.nf.ControlTreeWalker.walk(ControlTreeWalker.java:130)
      at com.bea.netuix.nf.Lifecycle.processLifecycles(Lifecycle.java:395)
      at com.bea.netuix.nf.Lifecycle.processLifecycles(Lifecycle.java:361)
      at com.bea.netuix.nf.Lifecycle.processLifecycles(Lifecycle.java:352)
      at com.bea.netuix.nf.Lifecycle.runInbound(Lifecycle.java:184)
      at com.bea.netuix.nf.Lifecycle.run(Lifecycle.java:159)
      at com.bea.netuix.servlets.manager.UIServlet.runLifecycle(UIServlet.java:388)
      at com.bea.netuix.servlets.manager.UIServlet.doPost(UIServlet.java:258)
      at com.bea.netuix.servlets.manager.UIServlet.service(UIServlet.java:199)
      at com.bea.netuix.servlets.manager.SingleFileServlet.service(SingleFileServlet.java:251)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
      at com.bea.console.utils.MBeanUtilsInitSingleFileServlet.service(MBeanUtilsInitSingleFileServlet.java:47)
      at weblogic.servlet.AsyncInitServlet.service(AsyncInitServlet.java:130)
      at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
      at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
      at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:292)
      at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
      at weblogic.servlet.internal.RequestEventsFilter.doFilter(RequestEventsFilter.java:27)
      at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
      at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3592)
      at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
      at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
      at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2202)
      at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2108)
      at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1432)
      at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
      at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)

      I followed the instruction in weblogic jdbc drivers guide to configure the JTA Transactions to support XA on SQL Server machine and weblogic server which included,

      1.Copying sqljdbc.dll copied to SQL_Server_Root/bin directory from WL_HOME\server\lib.
      2.Copied instjdbc.sql to sql server machine and executed the script with following output,

      Changed database context to 'master'.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_open', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_open2', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_close', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_close2', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_start', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_start2', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_end', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_end2', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_prepare', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_prepare2', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_commit', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_commit2', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_rollback', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_rollback2', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_forget', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_forget2', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_recover', because it does not exist or you do not have permission.
      Msg 3701, Level 11, State 5, Server SQLDB, Procedure sp_dropextendedproc, Line 16
      Cannot drop the procedure 'xp_jdbc_recover2', because it does not exist or you do not have permission.
      creating JDBC XA procedures

      instxa.sql completed successfully.

      3.Verified that MSDTC service is running on both SQL Server and weblogic machines with XA Transaction enabled and DTC option enabled for both inbound and outbound connections.

      4.Copied sqljdbc.jar (version 3.0 downloaded from msdn portal) to "C:\Oracle\Middleware\wlserver_10.3\server\ext\jdbc\sqlserver" directory and updated weblogic_classpth variable in commEnv.cmd file.

      set WEBLOGIC_CLASSPATH=%JAVA_HOME%\lib\tools.jar;%BEA_HOME%\utils\config\10.3\config-launch.jar;%WL_HOME%\server\lib\weblogic_sp.jar;%WL_HOME%\server\lib\weblogic.jar;%FEATURES_DIR%\weblogic.server.modules_10.3.2.0.jar;%WL_HOME%\server\lib\webservices.jar;%ANT_HOME%/lib/ant-all.jar;%ANT_CONTRIB%/lib/ant-contrib.jar;C:\Oracle\Middleware\wlserver_10.3\server\ext\jdbc\sqlserver\server\ext\jdbc\sqlserver\sqljdbc.jar

      Can some one please provide some input on whats causing this and any other steps needs to be followed to implement XA support using SQL Server JDBC driver.