Knowledge Article
Remedy AR System Server - ARS 9.x How to delete an Overlay Object from the Database
Remedy - Server - ARS 9.x How to delete an Overlay Object from the Database
I have a broken overlay form. I cannot delete it from Developer Studio and it returns error "overlay does not exist' when we try. Even sometime it does not show any error but can’t be deleted either.
How can I delete it manually from the database side?
I have a broken overlay form. I cannot delete it from Developer Studio and it returns error "overlay does not exist' when we try. Even sometime it does not show any error but can’t be deleted either.
How can I delete it manually from the database side?
Remedy AR System Server
AR System
BMC Remedy AR System Server 9.x
I have a broken overlay form. I cannot delete it from Developer Studio and it returns error "overlay does not exist' when we try. Even sometime it does not show any error but can’t be deleted either. How can I delete it manually from the database side? |
N/A
There are several things we need to do (besides taking a db backup before we risk anything): 1- Remove the overlayProp record from the arschema table. 2- Change the overlayProp for the schemaid in the arschema table. 3- Update the Views. 4- Update the Fields. 5- Remove orphan objects. Starting Remedy version 9.x, we will not see separate schemaid for Overlay form. We will share and maintain common schemaid between overlay and base object with visible difference in property level changes only. Safety Note: Please make sure you understand these steps before executing them, only because you are manipulating metadata and mistakes are very difficult to recover from. You may want to create a backup of the tables being worked: - For Oracle use the following syntax: Create table <newtable> as select * from <originaltable>; Example: ‘create table arschema_copy as select * from arschema;’ - For SQL Server or Sybase: Select * into <newtable> from <originaltable> Example: ‘Select * into <newtable> from <originaltable>’ Example using a SQL Client SQL statement: Select name, schemaId, overlayprop,overlayGroup from arschema where name like '%HPD:Help Desk' Note: We will use the HPD:Help Desk form as an example. Also, the SchemaID will be different on each case, the one displayed here is just as an example. We will see something like this: (Please notice that schemaid is common here with change in overlayprop and overlaygroup column values) Just want to look at the record once before deleting it. Always good to have a visual just to see that we’re deleting the right object. Record the object that has Overlayprop as 2 and OverlayGroup as 1 . We’ll refer to this later as <OverlayObject> Also record the object that has Overlayprop as 1 and OverlayGroup as 0 . We’ll refer to this later as <BaseObject> 1- Remove the overlayProp record from the arschema table - Delete the OverlayObject SQL Statement to use: delete from arschema where schemaId=<schemaid> and Overlayprop=2 2- Change the overlayProp for the schemaid in the arschema table - Set the overlayprop so that it thinks there is no overlay. Update the BaseObject, SQL Statement to use: update arschema set overlayProp=0 where overlayProp=1 and schemaid=<schemaid> 3- Update the Views - Since we are telling the form that it is not overlaid, we should also set the Views to be not-overlaid. So here update the BaseObject vui, SQL Statement to use: update vui set overlayProp=0 where overlayProp=1 and schemaId=<schemaid> 4- Update the Fields - Also do the same for field BaseObject SQL Statement to use: update field set overlayProp=0 where overlayProp=1 and schemaId=<schemaid> At this point we can either test or clean up the field tables. If it’s convenient, test now before we spend more time. 5- Remove orphan objects - To remove the orphans from the OverlayObject field and schema tables (note that many of these tables may not have any records in them). Delete from vui where schemaid=<schemaid> and OverlayGroup=1 and OverlayProp=2 Delete from field where schemaId=<schemaid> and Overlaygroup=1 Delete from field_attach where schemaId=<schemaid> and OverlayGroup=1 Delete from field_char where schemaId=<schemaid> and OverlayGroup=1 Delete from field_column where schemaId=<schemaid> and OverlayGroup=1 Delete from field_curr where schemaId=<schemaid> and OverlayGroup=1 Delete from field_date where schemaId=<schemaid> and OverlayGroup=1 Delete from field_dec where schemaId=<schemaid> and OverlayGroup=1 Delete from field_diary where schemaId=<schemaid> and OverlayGroup=1 Delete from field_display where schemaId=<schemaid> and OverlayGroup=1 Delete from field_dispprop where schemaId=<schemaid> and OverlayGroup=1 Delete from field_enum where schemaId=<schemaid> and OverlayGroup=1 Delete from field_enum_values where schemaId=<schemaid> and OverlayGroup=1 Delete from field_int where schemaId=<schemaid> and OverlayGroup=1 Delete from field_permissions where schemaId=<schemaid> and OverlayGroup=1 Delete from field_real where schemaId=<schemaid> and OverlayGroup=1 Delete from field_table where schemaId=<schemaid> and OverlayGroup=1 Delete from field_view where schemaId=<schemaid> and OverlayGroup=1 delete from schema_archive_association where schemaid= <schemaid> and overlayGroup=1 delete from schema_archive where schemaid= <schemaid> and overlayGroup=1 delete from schema_audit where schemaid= <schemaid> and overlayGroup=1 delete from schema_group_ids where schemaid= <schemaid> and overlayGroup=1 delete from schema_index where schemaid= <schemaid> and overlayGroup=1 delete from schema_list_fields where schemaid= <schemaid> and overlayGroup=1 delete from schema_sort where schemaid= <schemaid> and overlayGroup=1 delete from schema_join where schemaid=<schemaid> and overlayGroup=1 - Restart the AR System application. - Run again the very first query to verify that there is no longer an overlay for the related form: Select name, schemaId, overlayprop,overlayGroup from arschema where name like '%HPD:Help Desk' - Opening DevStudio, the form will be displayed as "unmodified" which is the expected result: |
Attachment(s):