Article ID: 924149
Article Last Modified on 10/27/2006
APPLIES TO
- Microsoft Systems Management Server 2003
SYMPTOMS
In Microsoft Systems Management Server (SMS) 2003, SMS clients that are members of a certain collection do not receive package advertisements.
You experience this problem if the following conditions are true:
- You advertise a package to a particular collection.
- You click to select the Include members of subcollections check box in the Advertisement Properties dialog box of the advertisement.
- The destination collection contains many deeply nested subcollections.
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.
WORKAROUND
To work around this problem, reduce the number of nested subcollections to a level that is appropriate for the SMS environment. You must test the particular SMS environment to determine the appropriate number of nested subcollections.
Keywords: kbtshoot kbdeployment kbprb KB924149
