Software Portal doesn't show any available software for most users

Products

• Software Management Solution

Issue/Introduction

Software Portal doesn't show any available software for most users. In the log the following error is shown:
Unable to load published software for current user due to exception: System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Cause

This is caused by Software components having two assigned resource types, both Software Component and Software Release. No resource should have more than one resource type.

Resolution

Use the following SQL to identify what components have this issue.

select *
from RM_ResourceSoftware_Component
where Guid in (
    select guid
    from vRM_Software_Component_Item
    group by guid
    having count(Guid) >1)

To delete one of the resource types, run the following SQL (NOTE: A backup of the database should be completed before running this as a safety precaution):

INSERT INTO ItemToDelete
select Guid, getdate()
from RM_ResourceSoftware_Component
where Guid in (
    select guid
    from vRM_Software_Component_Item
    group by guid
    having count(Guid) >1)

NOTE: If there are associations to these software components Managed Delivery Policies may become broken and need to be recreated.