Stack trace error when accessing the Application Audit Report

Issue Details

Some customers have reported encountering an error on their xMatters systems using SQL Server. When attempting to access the Application Audit Report (for example, for Group or Group Supervisor), they encounter a stack trace similar to the following:

Your Request Was Unsuccessful Because Of The Following:

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [(SELECT 'CURRENT' AS ACTION, s.GRP_ID, r.TARGET_NAME AS GROUP_NAME, s.DESCRION, s.STATUS, s.COMPLETE, s.DUPLICATED, s.USE_EMERG_DVC, s.ORG_TZ_ID, s.WEB_UPDATED, s.WHO_UPDATED, s.WHEN_UPDATED, s.WHO_CREATED, s.WHEN_CREATED FROM GRP s, RECIPIENTS r  WHERE s.GRP_ID IN (SELECT RECIPIENT_ID FROM RECIPIENTS WHERE ORG_ID = 1)  AND s.DELETED_ID = -1  AND r.RECIPIENT_ID = s.GRP_ID AND (s.WHEN_UPDATED > ? ) AND (s.WHEN_UPDATED < ? )   UNION ALL SELECT s.ACTION, s.GRP_ID, r.TARGET_NAME AS GROUP_NAME, s.DESCRION, s.STATUS, s.COMPLETE, s.DUPLICATED, s.USE_EMERG_DVC, s.ORG_TZ_ID, s.WEB_UPDATED, s.WHO_UPDATED, s.WHEN_UPDATED, s.WHO_CREATED, s.WHEN_CREATED FROM GRP_REV s, RECIPIENTS_REV r  WHERE s.GRP_ID IN (SELECT RECIPIENT_ID FROM RECIPIENTS WHERE ORG_ID = 1) AND r.REV_SEQ=(select max(r2.rev_seq) from recipients_rev r2 where r2.recipient_id=s.grp_id and r2.when_updated <= s.when_updated and r.recipient_id = r2.recipient_id) AND (s.WHEN_UPDATED > ? ) AND (s.WHEN_UPDATED < ? )  ) ORDER BY GRP_ID DESC, WHEN_UPDATED DESC]; SQL state [S1000]; error code [209]; Ambiguous column name 'WHEN_UPDATED'.; nested exception is java.sql.SQLException: Ambiguous column name 'WHEN_UPDATED'.

......

Caused by: java.sql.SQLException: Ambiguous column name 'WHEN_UPDATED'.

......

Caused by: java.sql.SQLException: ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Cause

This issue occurs when the "Compatibility Level" parameter in SQL Server is set too low; i.e., to "SQL Server 2000 (80)".

Solution

To resolve this issue, you must change the compatibility level of the xMatters database.

To configure your database:

  1. Log into the Microsoft SQL Server Management Studio, and navigate to the xMatter database.
  2. Click Properties > Options.
  3. Change the Compatibility level setting to the highest available version.
    • For example, if you are using SQL Server 2008 or SQL Server 2008 R2, select SQL Server 2008 (100).
  4. Save your changes.
  5. Restart all xMatters web servers.

xMatters Reference

DTN-2982, SUP-6104, JDN-3834

Originally created by Don Clark

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk