Self-Solve Knowledge Search

Minimize Maximize
How to rename duplicated parameter values automatically for upgrading from QC 10...
This document has not been formally reviewed for accuracy and is provided as is
Title :
How to rename duplicated parameter values automatically for upgrading from QC 10 to ALM 11/11.52
Document ID :
KM00958833
Product - Version:
quality center 10.00 ;
OS :
Updated :
Tue May 27 08:43:18 GMT 2014
Summary :
The upgrade/Repair/verify fails with the following messages: Error: The verify log states: ALM-U#####: Some business components have parameters that are only differentiated by the case sensitivity of their name. Correct this by manually renaming one of the duplicate parameters. Customer found that he has a large number of duplicated parameter, and does not want to fix it manually. Thus, he wants to have some SQL queries to run automatically.
Problem

The upgrade/Repair/verify fails with the following messages:

Error:
The verify log states:
ALM-U#####: Some business components have parameters that are only differentiated by the case sensitivity of their name. Correct this by manually renaming one of the duplicate parameters.

Customer found that he has a large number of duplicated parameter, and does not want to fix it manually. Thus, he wants to have some SQL queries to run automatically.

Cause
The server detected duplication of component parameter names in the project. Even though the database is case sensitive (meaning, lowercase and uppercase letters are interpreted as different characters), component parameter names must be unique regardless of capitalization.
Fix

All steps should be done in QC 10 environment before doing upgrade to ALM 11/11.5.

Step 1: Find duplicated params
SELECT CO_ID,CO_NAME,PARAMNAME
FROM COMPONENT
JOIN (
  SELECT COUNT(1) CNT,LOWER(FP_NAME) PARAMNAME
      ,FP_COMPONENT_ID
  FROM FRAMEWORK_PARAM
  GROUP BY FP_COMPONENT_ID
      ,LOWER(FP_NAME)
  ) A ON FP_COMPONENT_ID = CO_ID
WHERE CNT > 1
=> Result: CO_ID = 5

Step 2: Retrieve all duplicated params
SELECT * FROM FRAMEWORK_PARAM WHERE FP_COMPONENT_ID = 5
=> Results can be like big, BIG, Big...

Step 3: Update all duplicated params by put a number at the end of its name
=> Result will be like big1, BIG2, Big3...

DECLARE @ID int, @A int, @Name varchar(255)
DECLARE My_Cursor CURSOR FOR
SELECT FP_ID,FP_NAME FROM FRAMEWORK_PARAM WHERE FP_COMPONENT_ID = 5
SET @A = 1

OPEN My_Cursor;
FETCH NEXT FROM My_Cursor into @ID, @Name

WHILE @@FETCH_STATUS = 0
   BEGIN
   UPDATE FRAMEWORK_PARAM
   SET FP_Name = @Name + CONVERT(varchar(255), @a)
   WHERE FP_ID = @ID
  
   FETCH NEXT FROM My_Cursor into @ID, @Name
   SET @a=@a+1
   END;
  
CLOSE My_Cursor;
DEALLOCATE My_Cursor;

SELECT * FROM FRAMEWORK_PARAM
GO