Description of the Project Server 2010 hotfix package (Pjsrvwfe-x-none.msp): March 9, 2012

USE <ProjectServer_Published>
SELECT CFV.PROJ_UID, MP.PROJ_NAME, CFV.MD_PROP_UID, CFPV.MD_PROP_NAME, COUNT (*) TOTALCOUNT FROM MSP_PROJ_CUSTOM_FIELD_VALUES AS CFV
INNER JOIN MSP_PROJECTS AS MP ON CFV.PROJ_UID=MP.PROJ_UID
INNER JOIN MSP_CUSTOM_FIELDS_PUBLISHED_VIEW AS CFPV
ON CFV.MD_PROP_UID=CFPV.MD_PROP_UID
where CFPV.MD_PROP_MAX_VALUES=1
GROUP BY CFV.PROJ_UID,MP.PROJ_NAME, CFV.MD_PROP_UID, CFPV.MD_PROP_NAME HAVING COUNT (*) >1
ORDER BY TOTALCOUNT DESC

USE <ProjectServer_Published>
SELECT * INTO MSP_PROJ_CUSTOM_FIELD_VALUES_BACKUP FROM MSP_PROJ_CUSTOM_FIELD_VALUES

USE <ProjectServer_Published>
DECLARE @ITERATIONS AS INT
SET @ITERATIONS=
(SELECT TOP 1 COUNT (*) TOTALCOUNT FROM MSP_PROJ_CUSTOM_FIELD_VALUES AS CFV 
INNER JOIN MSP_PROJECTS AS MP ON CFV.PROJ_UID=MP.PROJ_UID 
INNER JOIN MSP_CUSTOM_FIELDS_PUBLISHED_VIEW AS CFPV ON CFV.MD_PROP_UID=CFPV.MD_PROP_UID 
INNER JOIN MSP_CUSTOM_FIELDS AS CF ON CFV.MD_PROP_UID = CF.MD_PROP_UID 
WHERE CF.MD_PROP_MAX_VALUES=1 
GROUP BY CFV.PROJ_UID,MP.PROJ_NAME, CFV.MD_PROP_UID, CFPV.MD_PROP_NAME 
HAVING COUNT (*) >1 
ORDER BY TOTALCOUNT DESC )-1

IF @ITERATIONS IS NULL
BEGIN
PRINT 'DID NOT FIND ANY DUPLICATES TO PROCESS'

END
ELSE
BEGIN

PRINT 'TOTAL ITERATIONS TO PROCESS: ' 
PRINT @ITERATIONS

WHILE @ITERATIONS <>0
BEGIN
PRINT 'ITERATION COUNT: ' 
PRINT @ITERATIONS

DECLARE @PROJ_UID AS UNIQUEIDENTIFIER
DECLARE @MD_PROP_UID AS UNIQUEIDENTIFIER
DECLARE @MOD_DATE AS DATETIME 
DECLARE ACDELETEDUPLICATERECORDS CURSOR FOR 

SELECT PROJ_UID, MD_PROP_UID, MIN(MOD_DATE) AS MOD_DATE FROM MSP_PROJ_CUSTOM_FIELD_VALUES WHERE PROJ_UID IN
(
SELECT CFV.PROJ_UID FROM MSP_PROJ_CUSTOM_FIELD_VALUES AS CFV 
INNER JOIN MSP_PROJECTS AS MP ON CFV.PROJ_UID=MP.PROJ_UID 
INNER JOIN MSP_CUSTOM_FIELDS_PUBLISHED_VIEW AS CFPV 
ON CFV.MD_PROP_UID=CFPV.MD_PROP_UID 
INNER JOIN MSP_CUSTOM_FIELDS AS CF 
ON CFV.MD_PROP_UID = CF.MD_PROP_UID 
WHERE CF.MD_PROP_MAX_VALUES=1 
GROUP BY CFV.PROJ_UID,MP.PROJ_NAME, CFV.MD_PROP_UID, CFPV.MD_PROP_NAME HAVING COUNT (*) >1 
)
AND MD_PROP_UID IN 
(
SELECT CFV.MD_PROP_UID FROM MSP_PROJ_CUSTOM_FIELD_VALUES AS CFV 
INNER JOIN MSP_PROJECTS AS MP ON CFV.PROJ_UID=MP.PROJ_UID 
INNER JOIN MSP_CUSTOM_FIELDS_PUBLISHED_VIEW AS CFPV 
ON CFV.MD_PROP_UID=CFPV.MD_PROP_UID 
INNER JOIN MSP_CUSTOM_FIELDS AS CF 
ON CFV.MD_PROP_UID = CF.MD_PROP_UID 
WHERE CF.MD_PROP_MAX_VALUES=1 
GROUP BY CFV.PROJ_UID,MP.PROJ_NAME, CFV.MD_PROP_UID, CFPV.MD_PROP_NAME HAVING COUNT (*) >1 
)

GROUP BY PROJ_UID, MD_PROP_UID
HAVING COUNT (*) >1 
ORDER BY PROJ_UID

OPEN ACDELETEDUPLICATERECORDS 
FETCH NEXT FROM ACDELETEDUPLICATERECORDS 
INTO @PROJ_UID, @MD_PROP_UID, @MOD_DATE 
WHILE @@FETCH_STATUS =0 

BEGIN 

DELETE FROM MSP_PROJ_CUSTOM_FIELD_VALUES 
WHERE PROJ_UID=@PROJ_UID
AND MD_PROP_UID=@MD_PROP_UID
AND MOD_DATE=@MOD_DATE

FETCH NEXT FROM ACDELETEDUPLICATERECORDS 
INTO @PROJ_UID, @MD_PROP_UID, @MOD_DATE 

END 

CLOSE ACDELETEDUPLICATERECORDS 
DEALLOCATE ACDELETEDUPLICATERECORDS 

SET @ITERATIONS = @ITERATIONS-1
END 
END 

USE <ProjectServer_Published>
DELETE FROM MSP_PROJ_CUSTOM_FIELD_VALUES
INSERT INTO MSP_PROJ_CUSTOM_FIELD_VALUES
SELECT * FROM MSP_PROJ_CUSTOM_FIELD_VALUES_BACKUP

USE <ProjectServer_Published>
DROP TABLE MSP_PROJ_CUSTOM_FIELD_VALUES_BACKUP