Client Support
cancel

Unable to delete old record types on longer being used

SOLVED
Go to solution
Highlighted
Rich_Kid
Outstanding Contributor.

Unable to delete old record types on longer being used

Hi

 

I am cleaning up an old dataset (from Trim 6.x that now living in Trim 7) that has a large number of record types that no longer have records associated with them (the records that were created with these Record Types have been changed to another record type) and I have removed all associations with the additional fields that were associated with that type.

 

When I try and delete the record type, I get a workgroup error indicating a SQL Constraint error.

 

Details :The DELETE statement conflicted with the REFERENCE constraint "FK_rfRecTypeUri". The conflict occurred in database "TRIM", table "dbo.TSRTYFORM", column 'rfRecTypeUri'.

 Detailed ADO Errors  Number  = 0x80040e2f  Source  = Microsoft SQL Server Native Client 10.0  Description = The DELETE statement conflicted with the REFERENCE constraint "FK_rfRecTypeUri". The conflict occurred in database "TRIM", table "dbo.TSRTYFORM", column 'rfRecTypeUri'.  SQLState  = 23000  NativeError = 547  Number  = 0x80040e2f  Source  = Microsoft SQL Server Native Client 10.0  Description = The statement has been terminated.  SQLState  = 01000  NativeError = 3621

 

A new record type can be created and deleted in the client.

 

I think TSRTYFORM has something to do with additional fields, but there is no additional field on the form or associated with the record type. This happens with all these old record types I am trying to remove, including one record type that was never use for records and never had any additional fields associated with it.

 

Deleting the corresponding entry directly out of the TSRTYFORM table will allow me to remove the record type in the client, but I'm thinking the constraint must be there for a reason, I just don't get it!

 

Cheers

 

 

 

 

8 REPLIES
Grundy
Acclaimed Contributor.

Re: Unable to delete old record types on longer being used

Try removing every field from the RecType > Form tab so that only the title field is there.

Then attempt to delete the record type.



::::::::::::::::::::::
NOT A HP EMPLOYEE
::::::::::::::::::::::

INFORMOTION.com.au
Philip Harney
Outstanding Contributor.
Solution

Re: Unable to delete old record types on longer being used

Hello,

 

You didn't clean up all your old tables, views, references when you upgraded.  You have to run the schema repair tool and remove the tables view's etc, read the logs and make sure that you don't get any errors.

 

When your deleting the record type they still have a key constraint attached that is not used anymore hence the error.

 

Phil

Rich_Kid
Outstanding Contributor.

Re: Unable to delete old record types on longer being used

Many thanks Phil,

 

Didn't twig that there was a schema issue. The upgrade from 6.2.x to 7.1.x forces you to do a schema upgrade so I assumed the schema was good from this. Obviously the upgrade adds what's needed but does not remove whats redundant. ( Would be kind of nice if it prompted this step as well ... )

 

Looking at the repair log I get

 

11:38:08 AM 47      Removing obsolete table TSRTYFORM

11:38:08 AM 0       DROP PROCEDURE GetRTYFORM;

11:38:08 AM 47      DROP TABLE TSRTYFORM;

 

and now all is good, I can now delete those record types.

 

Cheers

 

 

 

 

Grundy
Acclaimed Contributor.

Re: Unable to delete old record types on longer being used

Great, Kudos Phil!



::::::::::::::::::::::
NOT A HP EMPLOYEE
::::::::::::::::::::::

INFORMOTION.com.au
Philip Harney
Outstanding Contributor.

Re: Unable to delete old record types on longer being used

Hey Grundy,


You may want to get someone to look at the order the removing of the tables and index's is done in the Schema repair, I had to run it 3 times to get rid of all the tables as it tries to remove a table that has links from another table that its actually trying to delete as well.

 

Really though its so much better then the old schema repair I can deal with having to re run it to get rid of all unused tables.

Grundy
Acclaimed Contributor.

Re: Unable to delete old record types on longer being used

I havn't seen that behaviour myself.

You got some logs you can send through to support?



::::::::::::::::::::::
NOT A HP EMPLOYEE
::::::::::::::::::::::

INFORMOTION.com.au
Sander Hoogwerf
Outstanding Contributor.

Re: Unable to delete old record types on longer being used

On my test upgrade to 7.1 I encountered these errors.

 

In the schema upgrade log there was an ORA-02449 when trying to DROP TABLE TSRTYFORM, which I didn't notice at the time.

 

When trying to repair the schema (full options ticked) it stopped after 39 hours on an ORA-02273 on trying to DROP the primary key PKTSRECTYPE. I then noticed that it referenced FK_RFRECTYPEURI that still was there because the DROP table during the schema upgrade didn't occur. I then dropped the foreign key manually and restarted the repair which completed OK after another day and a half.

 

So it seems that because the foreign key isn't dropped specifically during the schema upgrade, there are some additional issues that can pop up like described in this thread.


(Any opinions expressed in this forum are my own personal opinion and should not be construed as an official statement by DXC Technology.)

Analytics & Data Management
Application & Business Services
DXC Technology
MatthewWilson
Respected Contributor.

Re: Unable to delete old record types on longer being used

Just bumping this because I'm having the same issue.  I'm ok with deleting the relevant row from TSRTYFORM because it doesn't appear to do anything, but I'm searching old workgroup service logs to find out how they've been used first (and it'll go through TEST and change control before we do it in production)

 

I am quite sure that we did do a schema repair when we upgrade from 6.x to 7.1.0 (#1157).    I think I even still have the output logs.

 

Thanks!