|
jnelson993 -> RE: Problem with report 92 (6/9/2008 2:18:39 PM)
|
Man, I'm having Deja Vu...we just had this conversation with someone...oh, looks like that one was never resolved.
Well, execute this code in SQL Management Studio (or Query Analyzer if you're using SQL 2000) against the CM database. Modify the SET statements in this code to put in the same displayname & collid that you've been putting into the report, and also put in the resourceID of a machine that you know is receiving dupes.
What you'll get are 3 result sets, one that shows the records that come back from v_R_System, one that comes back from v_Add_Remove_Programs, and one that comes back from v_FullCollectionMembership. To find out which view is giving you the duplicates, you look at the results for each of the result sets. If all of the fields all the way across are duplicated identically for each duplicate row, it's NOT the source of your dupes. If, however, one of the fields in a row is different than the same field in the other rows, then that is the source of duplicates. Let us know how it goes.
DECLARE @DisplayName VARCHAR(128)
DECLARE @CollID VARCHAR(8)
DECLARE @ResourceID INT
SET @DisplayName = 'Microsoft Office Enterprise 2007'
SET @CollID = 'xxxxxxxx'
SET @ResourceID = 00000000
SELECT
sys.*
FROM
v_R_System sys
JOIN v_Add_Remove_Programs arp
ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm
ON sys.ResourceID = fcm.ResourceID
WHERE DisplayName0 = @displayname
AND fcm.CollectionID = @CollID
AND sys.resourceID = @ResourceID
SELECT
arp.*
FROM
v_R_System sys
JOIN v_Add_Remove_Programs arp
ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm
ON sys.ResourceID = fcm.ResourceID
WHERE DisplayName0 = @displayname
AND fcm.CollectionID = @CollID
AND sys.resourceID = @ResourceID
SELECT
fcm.*
FROM
v_R_System sys
JOIN v_Add_Remove_Programs arp
ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm
ON sys.ResourceID = fcm.ResourceID
WHERE DisplayName0 = @displayname
AND fcm.CollectionID = @CollID
AND sys.resourceID = @ResourceID
|
|
|
|
