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