7 Replies Latest reply on Sep 4, 2017 6:48 AM by mccloud

    JBPM 6.4.0.Final not working properly with MS SqlServer 2012 - Error Incorrect syntax near 'limit'

    franky051

      Hello,

       

      I configured JBPM 6.4.0.Final version to use with MS SqlServer 2012. I was able to start the JBOSS server with jbpm and kie workbench deployed successfully. When I looked at the Jboss server.log I am getting the below Exception "[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 2) Incorrect syntax near 'limit'". The stacktrace is pasted below. I think its the limit keyword which is not supported by SqlServer. I used the correct dialect and driver and tested this by writing a sample Java program. When I investigated further I am getting this when JBPM executed the below query again the Database. I got this query from SqlServer profiler. Please let me know how I can resolve this issue.

       

      Hibernate Dialect (Tried Both):

      org.hibernate.dialect.SQLServer2012Dialect  &  org.hibernate.dialect.SQLServerDialect

      Driver:

      sqljdbc4-2.0.jar

      Driver Class:

      com.microsoft.sqlserver.jdbc.SQLServerDriver

      xa-datasource-class

      com.microsoft.sqlserver.jdbc.SQLServerXADataSource

       

      Query Causing the problem:

      select requestinf0_.id as id1_31_, requestinf0_.commandName as commandN2_31_, requestinf0_.deploymentId as deployme3_31_, requestinf0_.executions as executio4_31_,

       

      requestinf0_.businessKey as business5_31_, requestinf0_.message as message6_31_, requestinf0_.owner as owner7_31_, requestinf0_.requestData as requestD8_31_,

       

      requestinf0_.responseData as response9_31_, requestinf0_.retries as retries10_31_, requestinf0_.status as status11_31_, requestinf0_.timestamp as timesta12_31_

      from RequestInfo requestinf0_ where (requestinf0_.status='QUEUED' or requestinf0_.status='RETRYING') and requestinf0_.timestamp<@P0

      and (requestinf0_.owner=@P1 or requestinf0_.owner is null) limit @P2 for update     

       

       

      Exception StackTrace:

       

      2017-02-02 13:26:27,190 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 2) Incorrect syntax near 'limit'.

       

      2017-02-02 13:26:27,206 WARN  [org.jbpm.shared.services.impl.TransactionalCommandService] (EJB default - 2) Could not commit session: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

      at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387) [hibernate-entitymanager-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310) [hibernate-entitymanager-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:316) [hibernate-entitymanager-4.2.21.Final.jar:4.2.21.Final]

      at org.jbpm.shared.services.impl.JpaPersistenceContext.queryStringWithParameters(JpaPersistenceContext.java:188) [jbpm-shared-services-6.4.0.Final.jar:6.4.0.Final]

      at org.jbpm.shared.services.impl.JpaPersistenceContext.queryAndLockWithParametersInTransaction(JpaPersistenceContext.java:65) [jbpm-shared-services-6.4.0.Final.jar:6.4.0.Final]

      at org.jbpm.executor.impl.jpa.ExecutorQueryServiceImpl$LockAndUpdateRequestInfoCommand.execute(ExecutorQueryServiceImpl.java:238) [jbpm-executor-6.4.0.Final.jar:6.4.0.Final]

      at org.jbpm.executor.impl.jpa.ExecutorQueryServiceImpl$LockAndUpdateRequestInfoCommand.execute(ExecutorQueryServiceImpl.java:224) [jbpm-executor-6.4.0.Final.jar:6.4.0.Final]

      at org.jbpm.shared.services.impl.TransactionalCommandService.execute(TransactionalCommandService.java:71) [jbpm-shared-services-6.4.0.Final.jar:6.4.0.Final]

      at org.jbpm.executor.impl.jpa.ExecutorQueryServiceImpl.getRequestForProcessing(ExecutorQueryServiceImpl.java:211) [jbpm-executor-6.4.0.Final.jar:6.4.0.Final]

      at org.jbpm.executor.impl.AvailableJobsExecutor.executeJob(AvailableJobsExecutor.java:36) [jbpm-executor-6.4.0.Final.jar:6.4.0.Final]

      at org.kie.server.jms.executor.PollExecutorBean.executeJob(PollExecutorBean.java:84) [kie-server-jms-6.4.0.Final.jar:6.4.0.Final]

      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_111]

      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_111]

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_111]

      at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_111]

      at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53)

      at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:407)

      at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:82) [wildfly-weld-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:93) [wildfly-weld-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53)

      at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ejb3.tx.EjbBMTInterceptor.handleInvocation(EjbBMTInterceptor.java:104) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.as.ejb3.tx.BMTInterceptor.processInvocation(BMTInterceptor.java:56) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:407)

      at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:55) [weld-core-impl-2.2.6.Final.jar:2014-10-03 10:05]

      at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:83) [wildfly-weld-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:45) [wildfly-ee-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)

      at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:53)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ejb3.component.interceptors.NonPooledEJBComponentInstanceAssociatingInterceptor.processInvocation(NonPooledEJBComponentInstanceAssociatingInterceptor.java:59) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:43) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:95) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:59) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:55) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.invocation.ContextClassLoaderInterceptor.processInvocation(ContextClassLoaderInterceptor.java:64)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:326)

      at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:448)

      at org.jboss.invocation.AccessCheckingInterceptor.processInvocation(AccessCheckingInterceptor.java:61)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:326)

      at org.jboss.invocation.PrivilegedWithCombinerInterceptor.processInvocation(PrivilegedWithCombinerInterceptor.java:80)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)

      at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:185)

      at org.jboss.as.ee.component.ViewDescription$1.processInvocation(ViewDescription.java:182)

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ejb3.component.interceptors.LogDiagnosticContextRecoveryInterceptor.processInvocation(LogDiagnosticContextRecoveryInterceptor.java:79) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:309)

      at org.jboss.as.ejb3.component.interceptors.AsyncFutureInterceptorFactory$1$2.runInvocation(AsyncFutureInterceptorFactory.java:97) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at org.jboss.as.ejb3.component.interceptors.AsyncInvocationTask.run(AsyncInvocationTask.java:73) [wildfly-ejb3-8.2.1.Final.jar:8.2.1.Final]

      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_111]

      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_111]

      at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_111]

      at org.jboss.threads.JBossThread.run(JBossThread.java:122)

      Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

      at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:88) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.loader.Loader.getResultSet(Loader.java:2062) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1859) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.loader.Loader.doQuery(Loader.java:906) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:348) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.loader.Loader.doList(Loader.java:2550) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.loader.Loader.doList(Loader.java:2536) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2366) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.loader.Loader.list(Loader.java:2361) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:198) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1230) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:287) [hibernate-entitymanager-4.2.21.Final.jar:4.2.21.Final]

      ... 80 more

      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'limit'.

      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)

      at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)

      at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)

      at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)

      at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)

      at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)

      at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)

      at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)

      at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)

      at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)

      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) [hibernate-core-4.2.21.Final.jar:4.2.21.Final]

      ... 95 more

       

       

      Thanks,

      Asim

        • 1. Re: JBPM 6.4.0.Final not working properly with MS SqlServer 2012 - Error Incorrect syntax near 'limit'
          abhijithumbe

          LIMIT is a MySQL reserved word,JDBC thinks it's talking to a MySQL database. Make sure you are passing hibernate dialect correctly

          • 2. Re: JBPM 6.4.0.Final not working properly with MS SqlServer 2012 - Error Incorrect syntax near 'limit'
            franky051

            I am using the correct MS SqlServer dialect. I tried with 2 different SqlServer Dialect and also tried with Sybase dialect. I also checked the persistence.xml within jbpm-console.war to see if the correct dialect was set and that file has SqlServer dialect. I dont understand why the limit keyword is appended in the sql query. This looks like a BUG within JBPM.

             

            Thanks,

            Asim

            • 3. Re: JBPM 6.4.0.Final not working properly with MS SqlServer 2012 - Error Incorrect syntax near 'limit'
              chandlerqueensu

              I got similar issue when using sql server. 

               

              any luck you solve this issue?

               

              2017-02-21 16:04:57,956 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 2) SQL Error: 102, SQLState: S0001

              2017-02-21 16:04:58,007 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 2) Incorrect syntax near 'limit'.

              2017-02-21 16:04:58,011 WARN  [org.jbpm.shared.services.impl.TransactionalCommandService] (EJB default - 2) Could not commit session: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

                at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)

                at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)

                at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:316)

                at org.jbpm.shared.services.impl.JpaPersistenceContext.queryStringWithParameters(JpaPersistenceContext.java:188)

                at org.jbpm.shared.services.impl.JpaPersistenceContext.queryAndLockWithParametersInTransaction(JpaPersistenceContext.java:65)

                at org.jbpm.executor.impl.jpa.ExecutorQueryServiceImpl$LockAndUpdateRequestInfoCommand.execute(ExecutorQueryServiceImpl.java:238)

                at org.jbpm.executor.impl.jpa.ExecutorQueryServiceImpl$LockAndUpdateRequestInfoCommand.execute(ExecutorQueryServiceImpl.java:224)

                at org.jbpm.shared.services.impl.TransactionalCommandService.execute(TransactionalCommandService.java:71)

                at org.jbpm.executor.impl.jpa.ExecutorQueryServiceImpl.getRequestForProcessing(ExecutorQueryServiceImpl.java:211)

                at org.jbpm.executor.impl.AvailableJobsExecutor.executeJob(AvailableJobsExecutor.java:36)

                at org.kie.server.jms.executor.PollExecutorBean.executeJob(PollExecutorBean.java:84)

                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                at java.lang.reflect.Method.invoke(Method.java:498)

                at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)

                at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:82)

                at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:93)

                at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ejb3.tx.EjbBMTInterceptor.handleInvocation(EjbBMTInterceptor.java:103)

                at org.jboss.as.ejb3.tx.BMTInterceptor.processInvocation(BMTInterceptor.java:58)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:437)

                at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:73)

                at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:83)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:45)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)

                at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:52)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:43)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:100)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:66)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:54)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.invocation.ContextClassLoaderInterceptor.processInvocation(ContextClassLoaderInterceptor.java:64)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356)

                at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:636)

                at org.jboss.invocation.AccessCheckingInterceptor.processInvocation(AccessCheckingInterceptor.java:61)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:356)

                at org.jboss.invocation.PrivilegedWithCombinerInterceptor.processInvocation(PrivilegedWithCombinerInterceptor.java:80)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61)

                at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:195)

                at org.jboss.as.ee.component.ViewDescription$1.processInvocation(ViewDescription.java:185)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ejb3.component.interceptors.LogDiagnosticContextRecoveryInterceptor.processInvocation(LogDiagnosticContextRecoveryInterceptor.java:82)

                at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)

                at org.jboss.as.ejb3.component.interceptors.AsyncFutureInterceptorFactory$1$2.runInvocation(AsyncFutureInterceptorFactory.java:101)

                at org.jboss.as.ejb3.component.interceptors.AsyncInvocationTask.run(AsyncInvocationTask.java:74)

                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

                at java.lang.Thread.run(Thread.java:745)

                at org.jboss.threads.JBossThread.run(JBossThread.java:320)

              Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

                at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)

                at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

                at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124)

                at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)

                at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:88)

                at org.hibernate.loader.Loader.getResultSet(Loader.java:2090)

                at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)

                at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1866)

                at org.hibernate.loader.Loader.doQuery(Loader.java:905)

                at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:347)

                at org.hibernate.loader.Loader.doList(Loader.java:2578)

                at org.hibernate.loader.Loader.doList(Loader.java:2564)

                at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2394)

                at org.hibernate.loader.Loader.list(Loader.java:2389)

                at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495)

                at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357)

                at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:198)

                at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1230)

                at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)

                at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:287)

                ... 74 more

              Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'limit'.

                at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)

                at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)

                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)

                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)

                at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)

                at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)

                at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)

                at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)

                at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:297)

                at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)

                at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)

                ... 89 more

              • 4. Re: JBPM 6.4.0.Final not working properly with MS SqlServer 2012 - Error Incorrect syntax near 'limit'
                dmarrazzo

                FROM: Installation Guide - Red Hat Customer Portal

                Change business-central/WEB-INF/classes/META-INF/persistence.xml.

                In this file, change the name of the Hibernate dialect used for your database, if using a different database other than H2. The code below demonstrates the original database information for persistence.xml:

                <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>

                This information can be updated in the following manner (as demonstrated with MySQL database below):

                <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>

                 

                CONFIGURING PERSISTENCE FOR THE INTELLIGENCE PROCESS SERVER

                 

                When changing the database for the Intelligence Process Server, the best practice is to use a separate database from the one for Business Central and Dashbuilder applications. To change the database for the Intelligence Process Server:

                 

                1. Open EAP_HOME/standalone/configuration/standalone.xml and locate the <system-properties> tag.
                2. Add the following properties:
                  • org.kie.server.persistence.ds: The JNDI name of your data source.
                  • class="simpara" style="margin-bottom:1em"

                <system-properties>

                   <property name="org.kie.server.repo" value="${jboss.server.data.dir}"/>

                   <property name="org.kie.example" value="true"/>

                   <property name="org.jbpm.designer.perspective" value="full"/>

                   <property name="designerdataobjects" value="false"/>

                   <property name="org.kie.server.user" value="bpmsUser"/>

                   <property name="org.kie.server.pwd" value="bpms123!"/>

                   <property name="org.kie.server.location" value="http://localhost:8080/kie-server/services/rest/server"/>

                   <property name="org.kie.server.controller" value="http://localhost:8080/business-central/rest/controller"/>

                   <property name="org.kie.server.controller.user" value="kieserver"/>

                   <property name="org.kie.server.controller.pwd" value="kieserver1!"/>

                   <property name="org.kie.server.id" value="local-server-123"/>

                 

                   <!-- Data source properties. -->

                   <property name="org.kie.server.persistence.ds" value="java:jboss/datasources/PostgresqlDS"/>

                   <property name="org.kie.server.persistence.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/> -->

                </system-properties>

                 

                 

                NOTE The dialect for DB2 is org.hibernate.dialect.DB2Dialect, for DB2 on AS/400 is org.hibernate.dialect.DB2400Dialect, for Oracle is org.hibernate.dialect.Oracle10gDialect, and for Microsoft SQL Server is org.hibernate.dialect.SQLServerDialect.

                 

                 

                 

                If this does not work, try opening a support case...

                • 5. Re: JBPM 6.4.0.Final not working properly with MS SqlServer 2012 - Error Incorrect syntax near 'limit'
                  sandesh.jp

                  I am also facing same issue, I am facing issue when i start kie-server.war application .

                  Any solution ?

                  • 6. Re: JBPM 6.4.0.Final not working properly with MS SqlServer 2012 - Error Incorrect syntax near 'limit'
                    huyrong

                    I have same error. I open new topic Configure BPM Suite used MS SQL and set unicode for task . Thank for any solution

                    • 7. Re: JBPM 6.4.0.Final not working properly with MS SqlServer 2012 - Error Incorrect syntax near 'limit'
                      mccloud

                      Did you try Donato's solution? For KIE-Server, you need to set a system property to set the dialect to be used by Hibernate.

                       

                         <property name="org.kie.server.persistence.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>

                       

                      For SQL Server 2012, that system property would need to be set to "SQLServer2012Dialect", like this:

                       

                         <property name="org.kie.server.persistence.dialect" value="org.hibernate.dialect.SQLServer2012Dialect"/>

                       

                      For Business-Central/jbpm-console you need to set that dialect in the "business-central/WEB-INF/classes/META-INF/persistence.xml"

                       

                      Cheers,

                       

                      Duncan