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 is run during the deltaupdatemembership schedule the following error is seen:

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.
 

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.

Cause

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

This issue has been reported to Symantec Development team. At this point this behavior will not be changed. It is working as design. Any attempt to fix this issue is trying to force something that is contrary to the design of the filter mechanism and SQL sub-queries.

Currently when such "unsupported" queries are detected, 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.

 

Applies To

Symantec Management Platform 8.x