Software Component, Release, Update sharing guids in multiple tables

Products

• Management Platform (Formerly known as Notification Server)
• Software Management Solution

Issue/Introduction

Software Component, Release, Update sharing guids in multiple tables

This can cause pickers to no load data

Items to show twice in a tree with a different icon 

Other apparently random symptoms

NA

Cause

A resource exists in two different tables where it should be in only one.  

For example, a software release name Awesome Notepad is in RM_ResourceSoftware_Release and RM_ResourceSoftware_Component

Environment

8.x

Resolution

The following script can be used in a SQL report within the Notification Server or in SQL Management Studio to see if the problem exists and what Software Components are in this state.

select sc.Guid as _ItemGuid, sc.Name, rt.Name as 'Resource Type' 
from vRM_Software_Component_Item sc
left join ResourceType rt on rt.Guid = sc.ResourceTypeGuid
where sc.Guid in
  (
    select Guid
    from vRM_Software_Component_Item
    group by Guid
    having COUNT (Guid) > 1
    and COUNT (distinct ResourceTypeGuid) > 1
  )
order by 1, 2, 3

After reviewing the list above this script can be used to remove duplicates from RM_ResourceSoftware_Component.  It is assumed that if another resource type has been defined that would be the desired out come.

IMPORTANT: Any time changes are made directly to the database it is a best practice to create a backup before making the changes.

delete from RM_ResourceSoftware_Component
where guid in
  (
    select Guid 
    from vRM_Software_Component_Item
    group by Guid
    having COUNT (Guid) > 1
    and COUNT (distinct ResourceTypeGuid) > 1
  )