Primary Resource Key violation causes SMP log Warning: Unable to manage the distribution points for package: <package name>

Products

• IT Management Suite

Issue/Introduction

When the “NS.Package Refresh..” schedule runs there are one-too-many warnings similar to the following thrown to the SMP logs as seen here:

Description: Unable to manage the distribution points for package: 'Surface Pro 3 W8 Platform Installer 15.10.26.0' (1f3ea218-91e8-4274-a23d-f18410d6c8d3)

Violation of PRIMARY KEY constraint 'PK__#Relevan__3AB8E8043CA553EE'. Cannot insert duplicate key in object 'dbo.#RelevantRT'. The duplicate key value is (367228db-3a4b-4564-bca4-4fbc188d1481).

The statement has been terminated.

   [System.Data.SqlClient.SqlException @ .Net SqlClient Data Provider]

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader()

   at Altiris.NS.DataAccessLayer.Implementation.Altiris_NS_StandardItems_SoftwareDelivery_DataAccessLayerSWDSupportDAL.spGetAutoAssignInfoForPackage__F(Guid PackageGuid, String PSSites)

   at Altiris.NS.DataAccessLayer.Implementation.Altiris_NS_StandardItems_SoftwareDelivery_DataAccessLayerSWDSupportDAL.spGetAutoAssignInfoForPackage(Guid PackageGuid, ICollection`1 PSSites)

   at Altiris.NS.StandardItems.SoftwareDelivery.SWDSupport.GetAutoAssignmentCollection(Guid packageGuid, ICollection`1 serverOrSiteGuids, ICollection`1 manuallyAssignedSites)

   at Altiris.NS.StandardItems.SoftwareDelivery.SWDSupport.SetPackageServerAssignment(Guid packageGuid, String packageName, SourceType st, String packageLocation, PkgSvrAssignmentType method, Boolean automaticallyAssignSites, ICollection`1 serverOrSiteGuids, ICollection`1 manuallyAssignedSites)

Environment

ITMS 8.x

Cause

When the warning was thrown—the stored procedure “spGetAutoAssignInfoForPackage”, was being invoked. The purpose of the procedure is to return all sites and targets that the individual package being evaluated during package refresh uses. The procedure declares a temporary table #RelevantRT, and also declares “ResourceTargetGuid” as the primary resource key. It then runs a query to insert all relevant/associated targets into the table but during the execution the same ResourceTargetGuid was getting returned twice, which violated the Primary Resource Key constraints.

The root cause was that there was a custom site created, and then manually assigned to the following target:

 “All Computers where the Cloud-enabled Management feature is enabled”.

This target is used by Cloud Enabled Management (CEM).

The condition where both sites had a “targetservedbysite” reference to the target is what violated the Primary Resource Key constraints.

Attached to this article is a query “spGetAutoAssignInfoForPackage_revised.sql” which when run against the database of the SMP, uses a sub-query without any PRK requirements, and will show which target is being returned twice and which sites have the reference.

As previously noted: In the case around which this article was written for there were two sites that had a reference to the target.

CEM                              {A custom site}

Default Internet Site       {The hidden site automatically assigned to the target during CEM configuration}

Resolution

Go into the "Manually Assigned Agents" section of the custom site and remove the assignment to the target and this should eliminate the problem.