topic SQL error using MSSQL as database in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/sql-error-using-mssql-as-database/m-p/201706#M154836 <HTML><HEAD></HEAD><BODY><SPAN>Running the following code throws an error with MSSQL.&nbsp; Runs fine with Oracle.&nbsp; Tested from 5.13 thru 5.16.1</SPAN><BR /><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ProcessEngine processEngine = ProcessEngines.getProcessEngine(dbname);</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RepositoryService repositoryService = processEngine</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.getRepositoryService();</SPAN><BR /><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;List&lt;ProcessDefinition&gt; processDefinitions = repositoryService</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.createProcessDefinitionQuery().active().latestVersion()</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.orderByProcessDefinitionName().asc()</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.orderByProcessDefinitionVersion().asc().list();</SPAN><BR /><BR /><SPAN>com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.custom.spring.CustomQuery.getResultSet(CustomQuery.java:45)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.custom.spring.CustomGroupEntityManager.findGroupsByUser(CustomGroupEntityManager.java:57)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.TaskQueryImpl.getGroupsForCandidateUser(TaskQueryImpl.java:827)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.TaskQueryImpl.getCandidateGroups(TaskQueryImpl.java:817)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at java.lang.reflect.Method.invoke(Unknown Source)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.reflection.invoker.MethodInvoker.invoke(MethodInvoker.java:37)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.reflection.wrapper.BeanWrapper.getBeanProperty(BeanWrapper.java:153)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.reflection.wrapper.BeanWrapper.get(BeanWrapper.java:44)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:123)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.DynamicContext$ContextMap.get(DynamicContext.java:89)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.DynamicContext$ContextAccessor.getProperty(DynamicContext.java:107)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.OgnlRuntime.getProperty(OgnlRuntime.java:1657)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.ASTProperty.getValueBody(ASTProperty.java:92)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:170)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:210)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.ASTNotEq.getValueBody(ASTNotEq.java:49)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:170)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:210)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.ASTAnd.getValueBody(ASTAnd.java:56)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:170)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:210)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:333)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:413)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:395)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.OgnlCache.getValue(OgnlCache.java:42)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.ExpressionEvaluator.evaluateBoolean(ExpressionEvaluator.java:32)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.IfSqlNode.apply(IfSqlNode.java:30)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.MixedSqlNode.apply(MixedSqlNode.java:29)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.IfSqlNode.apply(IfSqlNode.java:31)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.MixedSqlNode.apply(MixedSqlNode.java:29)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.TrimSqlNode.apply(TrimSqlNode.java:42)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.MixedSqlNode.apply(MixedSqlNode.java:29)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.MixedSqlNode.apply(MixedSqlNode.java:29)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.MixedSqlNode.apply(MixedSqlNode.java:29)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.builder.xml.dynamic.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:37)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:241)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:79)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:101)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:416)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:407)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.persistence.entity.TaskEntityManager.findTasksByQueryCriteria(TaskEntityManager.java:121)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.TaskQueryImpl.executeList(TaskQueryImpl.java:934)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.AbstractQuery.execute(AbstractQuery.java:139)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:24)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:57)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at org.activiti.engine.impl.AbstractQuery.list(AbstractQuery.java:113)</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; at com.xxx.workflow.Activiti.listUserTasks(Activiti.java:323)</SPAN></BODY></HTML> Mon, 25 Aug 2014 17:36:33 GMT tcheekva 2014-08-25T17:36:33Z SQL error using MSSQL as database https://connect.hyland.com/t5/alfresco-archive/sql-error-using-mssql-as-database/m-p/201706#M154836 Running the following code throws an error with MSSQL.&nbsp; Runs fine with Oracle.&nbsp; Tested from 5.13 thru 5.16.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ProcessEngine processEngine = ProcessEngines.getProcessEngine(dbname);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RepositoryService repositoryService = processEngine&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.getRepositoryService();&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;List&amp; Mon, 25 Aug 2014 17:36:33 GMT https://connect.hyland.com/t5/alfresco-archive/sql-error-using-mssql-as-database/m-p/201706#M154836 tcheekva 2014-08-25T17:36:33Z Re: SQL error using MSSQL as database https://connect.hyland.com/t5/alfresco-archive/sql-error-using-mssql-as-database/m-p/201707#M154837 <HTML><HEAD></HEAD><BODY><SPAN>Could you create a basic unit test that demonstrates this problem (I see for example CustomGroupEntityManager in the stacktrace) - so we can include this test in our multi-database QA?</SPAN></BODY></HTML> Sat, 30 Aug 2014 17:30:40 GMT https://connect.hyland.com/t5/alfresco-archive/sql-error-using-mssql-as-database/m-p/201707#M154837 jbarrez 2014-08-30T17:30:40Z