Delta Resource Membership will not update a Filter based on Raw SQL queries if they include DECLARE statements

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

When a filter that has a DECLARE statement in it that is run during the Delta Membership Update schedule the following error is seen:

Priority: 2;
Process: w3wp (3572);
Module: w3wp.exe
Source:
Altiris.NS.StandardItems.Collection.NSDataSrcBasedResourceCollection.DeltaUpdate
Membership
Description: Unexpected exception has occurred during the execution of the SQL for delta collection update for '290d80eb-d4a3-485a-987b-d265ac125e36'. Full update is run instead.

Environment

ITMS 8.x

Cause

This behavior is a limitation of SQL, where sub-queries do not support CREATE/DROP TABLE, DECLARE, IF EXISTS and INSERT clauses. For example, a DECLARE should be the first statement in the SQL query, so it cannot be part of a sub-query.

This is caused because all SQL queries run in the Delta Membership Update are run inside a sub-query which does not allow the use of DECLARE clauses.
 

 

Resolution

Symantec/Broadcom Development team has reviewed this issue and it is working as designed. Any attempt to fix this issue is trying to force something that is contrary to the design of the filter mechanism and Microsoft's SQL sub-queries.

Currently when such "unsupported" queries are detected, the SMP runs a FULL UPDATE instead of DELTA UPDATE for such items.

You can work around this problem by creating a view or a table function, and referencing that.