Uploaded image for project: 'EJBCA'
  1. EJBCA
  2. ECA-7154

SQL Grammar Exception on MS SQL Server v12

    Details

    • Sprint:
      EJBCA Sprint 18

      Description

      EJBCA 6.14 Alpha0 installation on the Azure Cloud running Microsoft SQL Server v12.00.600

      When visiting the publishers page we get the following exception:

      Hello,
      
      An exception has occurred.
      
      javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
                     at org.ejbca.core.ejb.ca.publisher.PublisherQueueData.findCountOfPendingEntriesForPublisher(PublisherQueueData.java:341)
                     at org.ejbca.core.ejb.ca.publisher.PublisherQueueSessionBean.getPendingEntriesCountForPublisherInIntervals(PublisherQueueSessionBean.java:197)
      Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
                     ... 137 more
      
      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
                     at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
                     at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)
                     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:548)
                     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:479)
                     at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7344)
                     at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2713)
                     at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224)
                     at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204)
                     at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:401)
                     at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:113)
                     at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
                     at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
                     ... 152 more
      

      Using mssql-jdbc-6.4.0.jre8.jar JDBC driver from Microsoft.

        Attachments

          Activity

            People

            • Assignee:
              bastianf Bastian Fredriksson
              Reporter:
              bastianf Bastian Fredriksson
              Verified by:
              Tomas Gustavsson
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 1 hour Original Estimate - 1 hour
                1h
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 3 hours
                3h