Error: Violation of PRIMARY KEY constraint 'PK_SWDPackageServer'. Cannot insert duplicate key in object 'dbo.SWDPackageServer'.

book

Article ID: 153036

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

When the Package Refresh schedule attempts to run it eventually throws an error in the NS logs, similar to the following, for each package it attempt to refresh

Unable to manage the distribution points for package "OOB Site Service Agent - Software Package Resource" (Guid {173fda6f-f1b5-44e5-a966-4899f46d652d}). Reason: Violation of PRIMARY KEY constraint 'PK_SWDPackageServer'. Cannot insert duplicate key in object 'dbo.SWDPackageServer'.

Cause

Possible Cause 1

 

The GUID of one or more of the site servers has somehow entered both of the following tables:

  • RM_Resourcevirtual_Machine
  • RM_ResourceComputer  

Explanation: Each managed computer should either be a Virtual Computer or just a Computer. When it submits a basic inventory it will be added to one of these tables or the other, but not both.

Part of the process of refreshing packages involves the running of stored procedure: "spSetAllPackageServers".  One of the queries run within the procedure performs an inner join with the vComputerResource, which ultimately pulls from both of the previously mentioned tables. As it does, since the GUID of the computer is in both tables, it is returned twice. 

The process is only expecting one distinct GUID for each package server and therefore, when the same GUID is returned more than one time it immediately violates the primary resource key constraints (pkgSvrId + PackageId) or (package server’s Guid + package Guid).

 

Possible Cause 2  There may be one or more site servers that has more than one "Site Service to Computer" resource associations. Each server should only have one. If that happens then it will be returned twice when the stored procedure is run and cause the primary resource key violation to occur.

Running the following query will help determine if this condition exists. Look for any computer name that is returned twice.


select  i1.Name as [Type], i2.Name as Parent, i3.Name as Child, ra.*
from ResourceAssociation ra
join vItem i1 on i1.Guid = ra.ResourceAssociationTypeGuid
join vItem i2 on i2.Guid = ra.ParentResourceGuid
join vItem i3 on i3.Guid = ra.ChildResourceguid
join vPackageServiceResource ps on ps.guid = ra.ParentResourceGuid
where ra.ResourceAssociationTypeGuid='5F00E96B-93F3-41f0-94A7-7DBBB8AEF841'
order by i3.Name

 

Resolution

Solution to Cause 1:

1. The following query is a modified excerpt of spSetAllPackageServers. Run it against the Symantec_CMDB. In the results look for a computer who’s name is returned multiple times:

DECLARE @PackageGuid uniqueidentifier

SET @PackageGuid = '5FA31642-CB4A-43DE-A538-CBB8B6E3BCB7' -- Altiris Agent Package
SELECT cid.Name, @PackageGuid, Resources.ResourceGuid, 0, 'Not Ready', NULL
FROM (select r.Guid as ResourceGuid, r.IsLocal
      from vComputer r
   join ResourceAssociation ra on r.Guid=ra.ChildResourceGuid
         and ra.ResourceAssociationTypeGuid = '5F00E96B-93F3-41f0-94A7-7DBBB8AEF841'
   join vPackageServiceResource ps on ra.ParentResourceGuid=ps.Guid
      ) Resources

LEFT OUTER JOIN SWDPackageServer ON Resources.ResourceGuid = SWDPackageServer.PkgSvrId
 AND SWDPackageServer.PackageId = '1cc0057e-314f-4438-a3f2-04a6c02d980f'

JOIN Inv_AeX_AC_Identification cid ON cid._Resourceguid = Resources.ResourceGuid

WHERE SWDPackageServer.PackageId IS NULL
 AND Resources.IsLocal = 1

ORDER BY cid.Name  

2. If the previous query returns a computer who’s name is returned more than once, then copy its GUID from the “ResourceGuid” column and plug it into the SET parameter of the following query:

DECLARE @guid uniqueidentifier
SET @guid = '<guid>'

SELECT vm.Guid, vm.Name
FROM rm_Resourcevirtual_Machine vm
join rm_ResourceComputer rc on rc.Guid = vm.Guid
WHERE vm.guid = @guid
 

3. If the computer’s GUID is returned in both tables then determine if that computer is a Virtual Computer, or just a standard Computer resource type and then delete it from the table it should not be in using the following syntax:

DELETE FROM RM_ResourceVirtual_Machine where Guid = '<machine guid>'

DELETE FROM RM_ResourceComputer where Guid = '<machine guid>'

! Important note: Delete from one table or the other but not both.

Finally, it is not yet clearly understood how the computer’s GUID made it into both tables, but this problem has been seen more frequently when virtual computers have had the Package Server agent installed and reinstalled on multiple occasions.

If the issue occurs time and again a trigger may need to be put in place to prevent the same GUID from existing in both tables.

See KB159392  for additional details

 

Solution to Cause 2: 

Attached to this article is a modified spSetAllPackageServers.  Run the SQL contained in the file against the SMP database which alters the procedure and instructs the process to insert each distinct Guid into the tables it is populating regardless of how many times it is returned. 

Otherwise the other option would be to continue by doing the following:

If a computer name is returned twice in the specified query then choose the one with the oldest date (in the last column) the copy the ChildResourceGuid column and the ParentResourceGuid and plug them into the following SQL statement.  (Be sure to have a backup of the database before running any script that deletes directly from the database)

delete from ResourceAssociation where ChildResourceGuid = '<ChildResourceGuid here>' and ParentResourceGuid = '<ParentResourceGuid here>' and ResourceAssociationTypeGuid='5F00E96B-93F3-41f0-94A7-7DBBB8AEF841'


Applies To

Symantec Management Platform 7 SP5

Attachments

spSetAllPackageServers.sql get_app