search cancel

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


Article ID: 162682


Updated On:


IT Management Suite


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

Description: Unable to manage the distribution points for package: 'Surface Pro 3 W8 Platform Installer' (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)




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 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 constraint.

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 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}



Going into the Manually Assigned Agents section of the custom site and removing the assignment to the target eliminated the problem.


DuplicateTargetReferences.sql get_app