0 votes

When upgrading from 7.0.25 to 10.2.31 the DbUpgrade.exe gives weird error

enter image description here

When looking the generated upgrade script, he tries to create twice the RCONT$EMPLOYEE view, the second time with a TEAM column that doesn't even exist and that points to LKCONTTEAM which doesn't exist either.
Also no trace of these non existing table and field are found in SYS
TABLES or SYS_FIELDS....

create view R_CONT$EMPLOYEE as select CONT$EMPLOYEE.*,
(select F_ENTITY_NAME from LK_PROJ_ENTITY L1 where L1.K_PROJ_ENTITY=CONT$EMPLOYEE.F_ENTITY) as R_F_ENTITY,
(select F_COST_CAT_NAME from LK_COST_CATEGORY L2 where L2.K_COST_CATEGORY=CONT$EMPLOYEE.F_COST_CATEGORY) as R_F_COST_CATEGORY,
(select F_STD_UTIL_RATE_ from LK_STD_UTIL_RATE L3 where L3.K_STD_UTIL_RATE=CONT$EMPLOYEE.F_STDUTILRATE) as R_F_STDUTILRATE from CONT$EMPLOYEE
go


create view R_CONT$EMPLOYEE as select CONT$EMPLOYEE.*,
(select TEAM from LK_CONT_TEAM L1 where L1.K_CONT_TEAM=CONT$EMPLOYEE.TEAM) as R_TEAM from CONT$EMPLOYEE
go

Somewhere there must be something in the DB that causes this but where can I look ?

asked in Database by (618 points)
edited by

2 Answers

0 votes
Best answer

The following SQL script solved the problem :

  Delete from ADMIN.SYS_TABLES WHERE NAME = 'CONT$EMPLOYEE';

  -- tables
    UPDATE ADMIN.SYS_TABLES
    SET K_TABLE = 11100
    WHERE K_TABLE = 80011;

    -- Fields 
    UPDATE ADMIN.SYS_Fields
    SET K_TABLE = 11100
    WHERE K_TABLE = 80011;

    -- CATEGORY
    UPDATE ADMIN.SYS_CATEGORIES
    SET K_CATEGORY = 11100
    WHERE K_CATEGORY = 80011;

Thanks !

answered by (618 points)
selected by
After having the problem myself, a little extra info : to avoid problems with security it might be best to also update ACC_RIGHTS.
+1 vote

Stijn.

LK_CONT_TEAM is a standard table, so it will be defined in the XML files and later be imported in SYS_FIELDS and SYS_TABLES.

I suggest to check the K_TABLE from all fields that refer to LK_CONT_TEAM, maybe R&D changed the table ID or something like that...

answered by (7.4k points)
the upgrade added this to the category :  


15:46:36.126>     alter table CONT$EMPLOYEE add D_START DATETIME NULL
15:46:36.132>     alter table CONT$EMPLOYEE add D_END DATETIME NULL
15:46:36.138>     alter table CONT$EMPLOYEE add ISACTIVE VARCHAR(1) NOT NULL DEFAULT '1'
15:46:36.194>     alter table CONT$EMPLOYEE add YEARLYCOST FLOAT NULL
15:46:36.201>     alter table CONT$EMPLOYEE add TEAM INTEGER NULL


Maybe because the standard table has the same K_TABLE as the new standard table ?
Something like that is indeed what you should aim for
K_TABLE is irrelevant I think

it's because there is now a standard CONT$EMPLOYEE category, but the client already had exactly the same as custom... so the upgrade changed the existing category

What's the best way to solve this ?
Ask R&D ?
Remove the custom CONT$EMPLOYEE from SYS_TABLES.
Update SYS_FIELDS and point every K_TABLE, K_LOOKUPTABLE towards the standard key.
Update again
1,249 questions
1,521 answers
1,859 comments
328 users