Monday, March 17, 2008

Object Name Exists Error Message

If you see an error message in the application that reads "Record can not be saved as another Record with the same name exists within the Business Object" and you can't find another record with the same name in the application, you may want to run the following SQL query to see if you have any records in the database where the spec_name field is null in the IBS_SPEC table. The query below will show a count of these records with the biggest offenders at the top of the list.

select count(*), type_name from IBS_SPEC
where spec_name is null
group by type_name
order by count(*) desc

2 comments:

Rob Zombron said...

Here is the SQL we used to correct the broken records:

update IBS_SPEC IBS1
SET SPEC_NAME
= ( SELECT WT.TRIRECORDNAMETX
from T_TRIWORKTASK WT , IBS_SPEC IBS2
WHERE WT.SPEC_ID = IBS2.SPEC_ID
AND IBS2.SPEC_NAME IS NULL
AND IBS1.SPEC_ID = IBS2.SPEC_ID)
WHERE SPEC_TEMPLATE_ID = 10008284
AND IBS1.SPEC_NAME IS NULL

COMMIT

update IBS_SPEC IBS1
SET OBJECT_PATH
= ( SELECT CONCAT('\',WT.TRIRECORDNAMETX)
from T_TRIWORKTASK WT , IBS_SPEC IBS2
WHERE WT.SPEC_ID = IBS2.SPEC_ID
AND IBS2.OBJECT_PATH IS NULL
AND IBS1.SPEC_ID = IBS2.SPEC_ID)
WHERE IBS1.SPEC_TEMPLATE_ID = 10008284
AND IBS1.OBJECT_PATH IS NULL

COMMIT

Rob Zombron said...

Here is an updated version of what we ran in production, fixing the issue for both work tasks and building equipment:

For Work Tasks:

UPDATE IBS_SPEC IBS
SET IBS.UPDATED_DATE = SYSDATE
,IBS.UPDATED_BY = 999999
,IBS.SPEC_NAME
=
( SELECT WT.TRIRECORDNAMETX
from T_TRIWORKTASK WT
WHERE WT.SPEC_ID = IBS.SPEC_ID
)
WHERE IBS.SPEC_TEMPLATE_ID = 10008284
AND IBS.SPEC_NAME IS NULL
COMMIT
UPDATE IBS_SPEC IBS
SET IBS.UPDATED_DATE = SYSDATE
,IBS.UPDATED_BY = 999999
,IBS.OBJECT_PATH
=
( SELECT CONCAT('\',WT.TRIRECORDNAMETX)
from T_TRIWORKTASK WT
WHERE WT.SPEC_ID = IBS.SPEC_ID
)
WHERE IBS.SPEC_TEMPLATE_ID = 10008284
AND IBS.OBJECT_PATH IS NULL

COMMIT

For Building Equipment:

UPDATE IBS_SPEC IBS
SET IBS.UPDATED_DATE = SYSDATE
,IBS.UPDATED_BY = 999999
,IBS.SPEC_NAME
=
( SELECT BLDG.TRIRECORDNAMESY
from T_TRIBUILDINGEQUIPMENT BLDG
WHERE BLDG.SPEC_ID = IBS.SPEC_ID
)
WHERE IBS.SPEC_TEMPLATE_ID = 10008338
AND IBS.SPEC_NAME IS NULL

COMMIT

UPDATE IBS_SPEC IBS
SET IBS.UPDATED_DATE = SYSDATE
,IBS.UPDATED_BY = 999999
,IBS.OBJECT_PATH
=
( SELECT CONCAT('\',BLDG.TRIRECORDNAMESY)
from T_TRIBUILDINGEQUIPMENT BLDG
WHERE BLDG.SPEC_ID = IBS.SPEC_ID
)
WHERE IBS.SPEC_TEMPLATE_ID = 10008338
AND IBS.OBJECT_PATH IS NULL

COMMIT