Sorry if this is the wrong channel to ask, but is there a workaround I could use to delete the procedure?
This error led one of our databases to become unusable. The procedure was created , cant be modifed or deleted, and is using characters not supported by the client's connection charset (which cannot be changed). This causes any connection to be refused by that client. (cannot transliterate).
Is there anyway to delete the procedure and avoid having to completely recreate the database?
1 - I might try to delete the appropriate entries from the RDB$ tables (after making a backup of the db ;-]), or
2 - see if other groups like IBSurgeon might be able to help, or
3 - use tool to extract schema to script, create new database without the offending SP and then use data pump tool to migrate the data
I seem to have worked around it, even tough it seems like risky business, I will post it since I opened the ticket and I saw this same problem being posted a few years ago.
1. backed up db
2. created a user domain with the same definitions as the offending system domain (CREATE DOMAIN RDB_SOURCE AS BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS)
3. updated all objects using the system domain to use the new user domain (update RDB$RELATION_FIELDS set RDB$FIELD_SOURCE = 'RDB_SOURCE' where RDB$FIELD_SOURCE = 'RDB$SOURCE')
4. deleted the procedure.
5. now the original system domain isnt being used anywhere else, it seems to have been auto-deleted, I guess its proposital behavior.
6. created a user domain with same definition and same name as the system domain (CREATE DOMAIN RDB$SOURCE AS BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS)
7. updated all objects using the user domain to use the new user domain (update RDB$RELATION_FIELDS set RDB$FIELD_SOURCE = 'RDB$SOURCE' where RDB$FIELD_SOURCE = 'RDB_SOURCE')
8. deleted the temporary user domain RDB_SOURCE
9. updated RDB$FIELDS table to set the new user domain as a system domain (update RDB$FIELDS set RDB$SYSTEM_FLAG = 1 where RDB$FIELD_NAME = 'RDB$SOURCE')
Just for the record, I never have messed with system objects before, nor do I plan to in the future, or would advise anyone to do it too :p.