Queries

  • Article

WQL Containing a Subquery That Returns an Array Property Fails

In SMS 2003, the SMS Provider handling of array properties has been improved. As a trade-off, SMS does not support WMI Query Language (WQL) statements with subqueries, which return an array property.

For example, the following WQL statement with a subquery, which is querying against the array property SystemOUName, fails:

SELECT * FROM SMS_R_SYSTEM WHERE SystemGroupName NOT IN (SELECT SystemGroupName FROM SMS_R_System where SystemGroupName = "DOMAINNAME\\SystemGroup1")

In the Smsprov.log, the SQL statement generated for this WQL is incorrect.

WORKAROUND:   Subqueries of WQL statements should always return a nonarray property.

For example, change the query above to the following:

SELECT * FROM SMS_R_SYSTEM WHERE ResourceId NOT IN (SELECT ResourceId FROM SMS_R_System where SystemGroupName = "DOMAINNAME\\SystemGroup1")

Collection Evaluator On SMS 2003 SP1 Or Earlier Version Site Might Report Errors For Collections Using Decommission Property

A new property has been added in SMS 2003 SP2, called Decommission. SMS 2003 SP1 or earlier version sites do not have this property. If you have SMS 2003 SP2 or later sites in the same hierarchy as any sites running SMS 2003 SP1 or earlier, then the child SMS 2003 SP1 or earlier version site might report the following error:

SMS Collection Evaluator failed to update the query rule "Decommission1" of collection "Decommission1"

Microsoft SQL Server reported SQL message 207, severity 16: [42S22][207][Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Decommissioned0'

This issue occurs when:

  • The SMS 2003 SP1 or earlier version site is the child site of the SMS 2003 SP2 or later site.

  • On the SMS 2003 SP2 or later site, you create a collection, and that collection uses the new system property Decommission.

WORKAROUND:    The following are options for working around this issue:

  • Upgrade the child site to SMS 2003 SP2 or later.

  • Do not create the collection using the Decommission property on the SMS 2003 SP2 site. You can create a query or use reporting on SMS 2003 SP2 site.

  • Ignore the error.