SMS clients that are members of a certain collection do not receive package advertisements in Systems Management Server 2003 (924149)

CAUSE

This problem occurs because there are many deeply nested subcollections. These sub-collections cause the SMS Offer Manager (SMS_OFFER_MANAGER) to perform a loop when it queries the Collection_SubCollections table of the SMS database. When this problem occurs, the Offer Manager performs a query that resembles the following logged query:

SQL>>>select SubCollectionID from Collection_SubCollections where CollectionID = 110 SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>set ansi_padding off; set ANSI_NULLS OFF; set quoted_identifier off; set DATEFORMAT mdy; select convert(int,@@spid) SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>set ansi_padding off; set ANSI_NULLS OFF; set quoted_identifier off; set DATEFORMAT mdy; select convert(int,@@spid) SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>select SubCollectionID from Collection_SubCollections where CollectionID = 110 SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>set ansi_padding off; set ANSI_NULLS OFF; set quoted_identifier off; set DATEFORMAT mdy; select convert(int,@@spid) SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>set ansi_padding off; set ANSI_NULLS OFF; set quoted_identifier off; set DATEFORMAT mdy; select convert(int,@@spid) SMS_OFFER_MANAGER <date> <time> AM 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>select SubCollectionID from Collection_SubCollections where CollectionID = 109 SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>set ansi_padding off; set ANSI_NULLS OFF; set quoted_identifier off; set DATEFORMAT mdy; select convert(int,@@spid) SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>set ansi_padding off; set ANSI_NULLS OFF; set quoted_identifier off; set DATEFORMAT mdy; select convert(int,@@spid) SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>select SubCollectionID from Collection_SubCollections where CollectionID = 110 SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>set ansi_padding off; set ANSI_NULLS OFF; set quoted_identifier off; set DATEFORMAT mdy; select convert(int,@@spid) SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>set ansi_padding off; set ANSI_NULLS OFF; set quoted_identifier off; set DATEFORMAT mdy; select convert(int,@@spid) SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>select SubCollectionID from Collection_SubCollections where CollectionID = 110 SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>set ansi_padding off; set ANSI_NULLS OFF; set quoted_identifier off; set DATEFORMAT mdy; select convert(int,@@spid) SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>set ansi_padding off; set ANSI_NULLS OFF; set quoted_identifier off; set DATEFORMAT mdy; select convert(int,@@spid) SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>select SubCollectionID from Collection_SubCollections where CollectionID = 106 SMS_OFFER_MANAGER <date> <time> 832 (0x0340)
SQL>>>>> Done. SMS_OFFER_MANAGER <date> <time> 832 (0x0340)

In this situation, the Offer Manager cannot process other advertisement requests. This problem occurs because Microsoft SQL Server is busy handling the current looping query.