SQL Syntax Error When Using ORDER BY Statements in own SQL Queries

Knowledgebase English KB
1
Valid from Report Server 3
Since the Report Server 3, it is no longer possible to specify a separate sort order in the SQL queries of individual SQL data sources using ORDER BY statements. Data sources configured in this way cause an SQL syntax error when executing the report template. This fact is also displayed as a hint during installation.

Cause: 
Version 3 of the Report Server allows you to configure your own user-defined relationships between tables in order to further customize hierarchical structures in reports. However, it was necessary to make a structural change when processing your own SQL queries, so that for technical reasons, you have to encapsulate your own defined SQL queries in subqueries.
A query in the form "SELECT * FROM ([your SQL input])" has been sent to the database. If the SQL syntax of the single query contained an ORDER BY statement, this statement was also part of the subquery when it was executed later. However, since ORDER BY statements are not allowed in subqueries, an SQL syntax error occurs.

Solution:
To restore the original behavior, so that you can continue to use sorting in the reports as usual, only minor adjustments are necessary:
  • The ORDER BY statements must be removed in your own SQL queries
  • The relevant report templates must be customized in the Report Server Designer to select sorting for each individual table (in the properties of the relevant table object)
Due to these adjustments, the Report Server then automatically adds the corresponding ORDER BY statement to the SQL query, so that the sorting is still carried out on the database side and therefore no performance losses are to be feared.
IDKBTE001348 KBTE001348