Failed to execute delta collection update for (FILTER NAME HERE). When ORDER BY is used

Products

• IT Management Suite

Issue/Introduction

Logs continually report warnings about filters running a full update instead of a delta.  

This can cause concerns about if filters are being updated as expected and possibly more overhead due to running a full update instead of a delta. Failed to execute delta collection update for 'FILTER NAME HERE' (00000000-0000-0000-0000-000000000000), full update will run instead.

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Environment

ITMS 8.x

Cause

When a Delta Update is run the query defined for each filter is run as a subquery that adds the results to a temp table.  Having an ORDER BY clause in a subquery is not allowed without other parameters being defined.

Resolution

When filters are displayed in the Symantec Management Platform Console, the resources are always ordered by the Name column.  Any ordering defined in the query is ignored.  Due to this an ORDER BY clause is not needed.
 
There are two options:

• Remove the ORDER BY clause in the filter query 
• Add TOP n where 'n' = a number of rows right after the SELECT and before the Guid column being returned