Error: "An error has occurred while displaying data" if the same resource is added twice into a Filter

Products

• IT Management Suite

Issue/Introduction

You have created a custom computer filter and after adding a resource the 2nd time into the same filter, the following error is observed:

"An error has occurred while displaying data. Check server logs for details."

Steps for reproducing the issue:

1. Create a simple Computer Filter.
2. Add New resource (or use an existing one in current filter resource) into this filter, so it will be added separately in the Include list:
   
   
3. Now if you try to add this resource again using Right-Click on Computer > Add to Filter  - and adding to the same filter - you will receive the error message referenced above. Technically this filter now has this resource added twice (filtered by name, and then by resource GUID).

In a Profiler trace you can see that we try to add the same GUID a second time. Something like this:

EXECUTE spCollectionIncludesResourceInsert @CollectionGuid='f49a25e1-3cd3-4f39-b7a3-d2d05a7b4846', @ResourceGuidList=N'e6d221d0-10c6-44b5-b6d0-e999a1bcb684
e6d221d0-10c6-44b5-b6d0-e999a1bcb684', @cleanImport=1

Thus the full error is actually:

Item save to DB failed: f49a25e1-3cd3-4f39-b7a3-d2d05a7b4846, (fromClone=False, user=example\smpservice)

Violation of PRIMARY KEY constraint 'PK_CCollectionIncludeResource'. Cannot insert duplicate key in object 'dbo.CollectionIncludeResource'. The duplicate key value is (f49a25e1-3cd3-4f39-b7a3-d2d05a7b4846, a116b6b0-d314-4a91-b7a6-4d9fcdebcf00).
The statement has been terminated.

This might require you to review the Stored Procedure to exclude existing resources to be re-added and/or provide a more detailed error/explanation as to why it fails (as this resource have been already added into this filter).

Environment

ITMS 8.5 RU3, RU4

Cause

Known issue. There was an issue with ensuring the uniqueness for all include/exclude values.

Resolution

A fix for this was added to the ITMS 8.6 Release.

There is a workaround for this issue.  There are 4 updated stored procedures that needs to be applied:

• spCollectionIncludesResourceInsert.sql
• spCollectionIncludesCollectionInsert.sql
• spCollectionExcludesResourceInsert.sql
• spCollectionExcludesCollectionInsert.sql

Download the attached "UpdatedStoredProcedures.zip" and run them in Microsoft SQL Management Studio. These will drop the existing ones and recreate them with the necessary fixes.