9 Replies Latest reply on Dec 5, 2005 5:01 PM by 3004

    Transient Sybase SET CHAINED Tx exception.

    3004
      Hi,
      I've been experiencing a bit of strange behavior with WebLogic 6.1 SP3
      and Sybase 11.9.2.4. We are using the Sybase driver that comes with WL
      6.1. We are seeing occasional SQLExceptions concerning transactional
      modes for stored procedures running in our non-Tx datasource and regular
      SQL run in our Tx-datasource. I got the following for the stored
      procedure call:
      =========================
      com.sybase.jdbc.SybSQLException: Stored procedure 'weeklyflow..getWeeklyComplexContacts' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.
      at com.sybase.tds.Tds.processEed(Tds.java)
      at com.sybase.tds.Tds.nextResult(Tds.java)
      at com.sybase.jdbc.ResultGetter.nextResult(ResultGetter.java)
      at com.sybase.jdbc.SybStatement.nextResult(SybStatement.java)
      at com.sybase.jdbc.SybStatement.queryLoop(SybStatement.java)
      at com.sybase.jdbc.SybCallableStatement.executeQuery(SybCallableStatement.java)
      at weblogic.jdbc.pool.PreparedStatement.executeQuery(PreparedStatement.java:51)
      at weblogic.jdbc.rmi.internal.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:56)
      at weblogic.jdbc.rmi.SerialPreparedStatement.executeQuery(SerialPreparedStatement.java:42)
      at org.ici.weeklyflow.dao.SyBaseWeeklyDataDAOImpl.getWeeklyComplexContacts(SyBaseWeeklyDataDAOImpl.java:3233)
      at org.ici.weeklyflow.ejb.WeeklyFlowSBean.getComplexContacts(WeeklyFlowSBean.java:137)
      at org.ici.weeklyflow.ejb.WeeklyFlowSBean_1tv6ke_EOImpl.getComplexContacts(WeeklyFlowSBean_1tv6ke_EOImpl.java:785)
      at org.ici.weeklyflow.ejb.WeeklyFlowSBean_1tv6ke_EOImpl_WLSkel.invoke(Unknown Source)
      at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:305)
      at weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerRef.java:93)
      at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:274)
      at weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest.java:22)
      at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
      at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
      =======================
      All of our stored procedures used for retrieving data have been created
      with the 'anymode' transaction mode. They will run correctly for a while
      and then occasionally the error will occur.

      The regular SQL in the Tx-datasource resulted in the following:
      =======================
      com.sybase.jdbc.SybSQLException: SET CHAINED command not allowed within multi-statement transaction.
      at com.sybase.tds.Tds.processEed(Tds.java)
      at com.sybase.tds.Tds.nextResult(Tds.java)
      at com.sybase.jdbc.ResultGetter.nextResult(ResultGetter.java)
      at com.sybase.jdbc.SybStatement.nextResult(SybStatement.java)
      at com.sybase.jdbc.SybStatement.updateLoop(SybStatement.java)
      at com.sybase.jdbc.SybStatement.executeUpdate(SybStatement.java)
      at com.sybase.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java)
      at com.sybase.tds.Tds.setOption(Tds.java)
      at com.sybase.jdbc.SybConnection.setAutoCommit(SybConnection.java)
      at weblogic.jdbc.jts.Connection.getOrCreateConnection(Connection.java:594)
      at weblogic.jdbc.jts.Connection.prepareStatement(Connection.java:115)
      at weblogic.jdbc.rmi.internal.ConnectionImpl.prepareStatement(ConnectionImpl.java:135)
      at weblogic.jdbc.rmi.SerialConnection.prepareStatement(SerialConnection.java:78)
      at org.ici.weeklyflow.dao.SyBaseWeeklyApplicationDAOImpl.removePrivilegedUser(SyBaseWeeklyApplicationDAOImpl.java:312)
      at org.ici.weeklyflow.ejb.WeeklyFlowSBean.removePrivilegedUser(WeeklyFlowSBean.java:322)
      at org.ici.weeklyflow.ejb.WeeklyFlowSBean_1tv6ke_EOImpl.removePrivilegedUser(WeeklyFlowSBean_1tv6ke_EOImpl.java:1049)
      at org.ici.weeklyflow.ejb.WeeklyFlowSBean_1tv6ke_EOImpl_WLSkel.invoke(Unknown Source)
      at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:305)
      at weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerRef.java:93)
      at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:274)
      at weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest.java:22)
      at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
      at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
      =====================================

      I saw other postings concerning this message but those seemed to be in
      regards to errors that occurred all the time, does anyone have any ideas?

      Thanks,
      -Ben DeVore
      Tallan, Inc.
        • 1. Re: Transient Sybase SET CHAINED Tx exception.
          3004
          You need a patch for SP3. Send an email to Joe Weinstein.

          Benjamin DeVore <ben.devore@tallan.com> wrote in message
          news:pan.2002.08.29.15.28.08.358098.2109@tallan.com...
          Hi,
          I've been experiencing a bit of strange behavior with WebLogic 6.1 SP3
          and Sybase 11.9.2.4. We are using the Sybase driver that comes with WL
          6.1. We are seeing occasional SQLExceptions concerning transactional
          modes for stored procedures running in our non-Tx datasource and regular
          SQL run in our Tx-datasource. I got the following for the stored
          procedure call:
          =========================
          com.sybase.jdbc.SybSQLException: Stored procedure
          'weeklyflow..getWeeklyComplexContacts' may be run only in unchained
          transaction mode. The 'SET CHAINED OFF' command will cause the current
          session to use unchained transaction mode.
          at com.sybase.tds.Tds.processEed(Tds.java)
          at com.sybase.tds.Tds.nextResult(Tds.java)
          at com.sybase.jdbc.ResultGetter.nextResult(ResultGetter.java)
          at com.sybase.jdbc.SybStatement.nextResult(SybStatement.java)
          at com.sybase.jdbc.SybStatement.queryLoop(SybStatement.java)
          at
          com.sybase.jdbc.SybCallableStatement.executeQuery(SybCallableStatement.java)
          at
          weblogic.jdbc.pool.PreparedStatement.executeQuery(PreparedStatement.java:51)
          at
          weblogic.jdbc.rmi.internal.PreparedStatementImpl.executeQuery(PreparedStatem
          entImpl.java:56)
          at
          weblogic.jdbc.rmi.SerialPreparedStatement.executeQuery(SerialPreparedStateme
          nt.java:42)
          at
          org.ici.weeklyflow.dao.SyBaseWeeklyDataDAOImpl.getWeeklyComplexContacts(SyBa
          seWeeklyDataDAOImpl.java:3233)
          at
          org.ici.weeklyflow.ejb.WeeklyFlowSBean.getComplexContacts(WeeklyFlowSBean.ja
          va:137)
          at
          org.ici.weeklyflow.ejb.WeeklyFlowSBean_1tv6ke_EOImpl.getComplexContacts(Week
          lyFlowSBean_1tv6ke_EOImpl.java:785)
          at
          org.ici.weeklyflow.ejb.WeeklyFlowSBean_1tv6ke_EOImpl_WLSkel.invoke(Unknown
          Source)
          at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:305)
          at
          weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerRef.java
          :93)
          at
          weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:274)
          at
          weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest.java:2
          2)
          at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
          at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
          =======================
          All of our stored procedures used for retrieving data have been created
          with the 'anymode' transaction mode. They will run correctly for a while
          and then occasionally the error will occur.

          The regular SQL in the Tx-datasource resulted in the following:
          =======================
          com.sybase.jdbc.SybSQLException: SET CHAINED command not allowed within
          multi-statement transaction.
          at com.sybase.tds.Tds.processEed(Tds.java)
          at com.sybase.tds.Tds.nextResult(Tds.java)
          at com.sybase.jdbc.ResultGetter.nextResult(ResultGetter.java)
          at com.sybase.jdbc.SybStatement.nextResult(SybStatement.java)
          at com.sybase.jdbc.SybStatement.updateLoop(SybStatement.java)
          at com.sybase.jdbc.SybStatement.executeUpdate(SybStatement.java)
          at
          com.sybase.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java
          )
          at com.sybase.tds.Tds.setOption(Tds.java)
          at com.sybase.jdbc.SybConnection.setAutoCommit(SybConnection.java)
          at weblogic.jdbc.jts.Connection.getOrCreateConnection(Connection.java:594)
          at weblogic.jdbc.jts.Connection.prepareStatement(Connection.java:115)
          at
          weblogic.jdbc.rmi.internal.ConnectionImpl.prepareStatement(ConnectionImpl.ja
          va:135)
          at
          weblogic.jdbc.rmi.SerialConnection.prepareStatement(SerialConnection.java:78
          )
          at
          org.ici.weeklyflow.dao.SyBaseWeeklyApplicationDAOImpl.removePrivilegedUser(S
          yBaseWeeklyApplicationDAOImpl.java:312)
          at
          org.ici.weeklyflow.ejb.WeeklyFlowSBean.removePrivilegedUser(WeeklyFlowSBean.
          java:322)
          at
          org.ici.weeklyflow.ejb.WeeklyFlowSBean_1tv6ke_EOImpl.removePrivilegedUser(We
          eklyFlowSBean_1tv6ke_EOImpl.java:1049)
          at
          org.ici.weeklyflow.ejb.WeeklyFlowSBean_1tv6ke_EOImpl_WLSkel.invoke(Unknown
          Source)
          at weblogic.rmi.internal.BasicServerRef.invoke(BasicServerRef.java:305)
          at
          weblogic.rmi.cluster.ReplicaAwareServerRef.invoke(ReplicaAwareServerRef.java
          :93)
          at
          weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:274)
          at
          weblogic.rmi.internal.BasicExecuteRequest.execute(BasicExecuteRequest.java:2
          2)
          at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
          at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
          =====================================

          I saw other postings concerning this message but those seemed to be in
          regards to errors that occurred all the time, does anyone have any ideas?

          Thanks,
          -Ben DeVore
          Tallan, Inc.
          • 2. Re: Transient Sybase SET CHAINED Tx exception.
            666705
            Just in case someone else runs into this same problem when running WebLogic with Sybase I'd like to note that the problem went away at our site when we upgraded a WebLogic 6.1 SP3 server to SP5. The server in question had jconn2.jar in the classpath both before and after the upgrade and that had not fixed the problem under SP3.

            Good luck on your own problems.
            • 3. Re: Transient Sybase SET CHAINED Tx exception.
              666705
              This can happen when calling set/getAutoCommit or set/getTrasactionIsolation on the connection , when using Sybase database, these method calls start a local transaction in sybase , which needs to be explictly rolled back , in later version of WLS we have worked around this problem in sybase by explictly calling rollback after we ivoked above set/get method in connection pool maintanence code within wls.

              hth
              /
              sree
              • 4. Re: Transient Sybase SET CHAINED Tx exception.
                666705
                Hello All,
                I am getting the same exception. I am using WLS 8.1 SP1 and would believe the problem with WLS 6.1 should not show up. can some one answer whether this is a weblogic issue or I would have to SET CHAIN OFF at Sybase end. Can you let me know what excatly you did to the stored proc to fix this.
                Thanks in anticipation,
                • 5. Re: Transient Sybase SET CHAINED Tx exception.
                  3004


                  Vaibhav Joshi wrote:
                  Hello All,
                  I am getting the same exception. I am using WLS 8.1 SP1 and would believe the problem with WLS 6.1 should not show up. can some one answer whether this is a weblogic issue or I would have to SET CHAIN OFF at Sybase end. Can you let me know what excatly you did to the stored proc to fix this.
                  Thanks in anticipation,
                  If you show the whole stacktrace that would be good.

                  • 6. Re: Transient Sybase SET CHAINED Tx exception.
                    666705
                    We are also getting same exception with 8.1 SP4.
                    Does anyone has come across a solution to the problem

                    stack trace as below for reference.

                    <EJB Exception occurred during invocation from home: com.ejb.entity.Bean_idcyv4_HomeImpl@1679a2 threw exception: javax.transaction.TransactionRolledbackException: EJB Exception:: javax.ejb.EJBException: nested exception is: com.sybase.jdbc2.jdbc.SybSQLException: Stored procedure 'sp_validate_data' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.

                    com.sybase.jdbc2.jdbc.SybSQLException: Stored procedure 'sp_validate_data' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.

                    at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636)
                    at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996)
                    at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
                    at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204)
                    at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187)
                    at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1642)
                    at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1625)
                    at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:91)
                    at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStatement.java:147)
                    at com.ejb.entity.Bean.ejbCreate(Bean.java:760)
                    at com.ejb.entity.Bean_dofyje_Impl.ejbCreate(Bean_dofyje_Impl.java:215)
                    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:324)
                    at weblogic.ejb20.manager.DBManager.create(DBManager.java:1183)
                    at weblogic.ejb20.manager.DBManager.remoteCreate(DBManager.java:1132)
                    at weblogic.ejb20.internal.EntityEJBHome.create(EntityEJBHome.java:249)
                    at com.ejb.entity.Bean_dofyje_HomeImpl.create(Bean_dofyje_HomeImpl.java:286)
                    at com.ejb.entity.AdapterBean.create(AdapterBean.java:114)
                    at com.ejb.entity.AdapterBean_idcyv4_EOImpl.create(AdapterBean_idcyv4_EOImpl.java:100)
                    at com.web.actions.ApprovalAction.execute(ApprovalAction.java:38)
                    at com.ejb.action.SubmitActionBean.execute(SubmitActionBean.java:53)
                    at com.ejb.action.SubmitActionBean_gbi9w_EOImpl.execute(SubmitActionBean_gbi9w_EOImpl.java:46)
                    at com.DefaultServlet.processAction(DefaultServlet.java:212)
                    at com.DefaultServlet.doGet(DefaultServlet.java:187)
                    at com.DefaultServlet.doPost(DefaultServlet.java:128)
                    at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
                    at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
                    at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1006)
                    at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:419)
                    at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:315)
                    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6718)
                    at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
                    at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
                    at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3764)
                    at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2644)
                    at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:219)
                    at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:178)
                    • 7. Re: Transient Sybase SET CHAINED Tx exception.
                      3004
                      Datta K wrote:

                      We are also getting same exception with 8.1 SP4.
                      Does anyone has come across a solution to the problem

                      stack trace as below for reference.

                      <EJB Exception occurred during invocation from home: com.ejb.entity.Bean_idcyv4_HomeImpl@1679a2
                      threw exception: javax.transaction.TransactionRolledbackException: EJB Exception::
                      javax.ejb.EJBException: nested exception is: com.sybase.jdbc2.jdbc.SybSQLException:
                      Stored procedure 'sp_validate_data' may be run only in unchained transaction mode.
                      The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.

                      com.sybase.jdbc2.jdbc.SybSQLException: Stored procedure 'sp_validate_data' may be
                      run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause
                      the current session to use unchained transaction mode.
                      Hi. It means what it says. Is sp_validate_data your procedure? Sybase stored procedures, by default,
                      are only allowed to run in the chained/unchained (autoCommit(false)/autoCommot(true)) mode in which
                      they were created. To get around this, look up the sybase tool sp_procxmode, and call it with your
                      procedure name(s) to allow Sybase to run them whether you're in a transaction or not.
                      Joe



                      at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636)
                      at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996)
                      at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
                      at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204)
                      at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187)
                      at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1642)
                      at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1625)
                      at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:91)
                      at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStatement.java:147)
                      at com.ejb.entity.Bean.ejbCreate(Bean.java:760)
                      at com.ejb.entity.Bean_dofyje_Impl.ejbCreate(Bean_dofyje_Impl.java:215)
                      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:324)
                      at weblogic.ejb20.manager.DBManager.create(DBManager.java:1183)
                      at weblogic.ejb20.manager.DBManager.remoteCreate(DBManager.java:1132)
                      at weblogic.ejb20.internal.EntityEJBHome.create(EntityEJBHome.java:249)
                      at com.ejb.entity.Bean_dofyje_HomeImpl.create(Bean_dofyje_HomeImpl.java:286)
                      at com.ejb.entity.AdapterBean.create(AdapterBean.java:114)
                      at com.ejb.entity.AdapterBean_idcyv4_EOImpl.create(AdapterBean_idcyv4_EOImpl.java:100)
                      at com.web.actions.ApprovalAction.execute(ApprovalAction.java:38)
                      at com.ejb.action.SubmitActionBean.execute(SubmitActionBean.java:53)
                      at com.ejb.action.SubmitActionBean_gbi9w_EOImpl.execute(SubmitActionBean_gbi9w_EOImpl.java:46)
                      at com.DefaultServlet.processAction(DefaultServlet.java:212)
                      at com.DefaultServlet.doGet(DefaultServlet.java:187)
                      at com.DefaultServlet.doPost(DefaultServlet.java:128)
                      at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
                      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
                      at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1006)
                      at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:419)
                      at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:315)
                      at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6718)
                      at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
                      at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
                      at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3764)
                      at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2644)
                      at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:219)
                      at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:178)
                      • 8. Re: Transient Sybase SET CHAINED Tx exception.
                        666705
                        Hi Joe,
                        Thanks!
                        We used following to resolve this issue.
                        exec sp_procxmode procedure_name, "anymode"
                        Datta
                        • 9. Re: Transient Sybase SET CHAINED Tx exception.
                          3004
                          Datta K wrote:

                          Hi Joe,
                          Thanks!
                          We used following to resolve this issue.
                          exec sp_procxmode procedure_name, "anymode"
                          Datta
                          Exactly. Glad to help.
                          Joe