Ghost tables in the database

Ghost tables in the database

  
I have an odd problem. I build a table through OutSystems named DRG_CRF10. I then went through SQL Server and deleted the table so that it would correctly order the columns. I then republished the oml and instead of it rebuilding DRG_CRF10, it built DRG_CRF13 (there is already an 11 and 12). None of the columns that were supposed to be in the table were there in the newly numbered table either.  Now, no matter what I do, I cannot get the system to build DRG_CRF10. I have checked the entire database and there aren't any tables with the name DRG_CRF10.  The odd thing is that data was still stored in the table that no longer existed.  I could query the data and add/update data in the table, but there was no way to check the data in SQL Server.  I then went through and rebuilt the entity from scratch and deleted DRG_CRF13 from SQL Server and the system.  When I republished, DRG_CRF13 reappeared (with none of the columns) and DRG_CRF14 now showed up with all the correct columns.

Is there a way to "force" OutSystems to build a table with a specific name, or build a table in SQL Server and just import it into OutSystems so that I can fix this ever growing problem?

Thanks.

Josh
Hi Joshua,

Have you checked if there is a VIEW with that name? During the generation of a user entity (database table) the Platform checks the existence of tables and views with the same name.

SELECT name FROM sysobjects WHERE xtype = 'V'  and name like '%DRG_CRF%'


Regards,
João Portela
João

Thank you for the response. 
I ran the script in SQL Server, and nithing returned other than the other tables and views I am already using.  Incidentally, I found out the reason why DRG_CRF13 kept appearing when I would republish my system actually.  What apparently happened was that DRG_CRF10, DRG_CRF11, and DRG_CRF12 all moved up one number (10 became 11, 11 became 12, and 12 became 13) .  After removing all of the tables from the system and removing them from SQL Server, I tried rebuilding them again, and the system numbered them higher again.  I ended up getting all the way to DRG_CRF17 before I decided that I needed to build everything from scratch again and name them differently.  As a temporary fix, I am rebuilding the table again from scratch and renaming it "DRG10".

Thanks.


Josh,

There are also OutSystems system tables wich keep track of the entities and attributes of the used tables. Problably before creating a table outsystems checks these internal outsystems tables and finds the old one so will create a new one with a different name. Mapping of entity names to sql names is done using these system tables. In ossys_Entity all entities are named, PHYSICAL_TABLE_NAME contains the real sql name. So just dumping tables in sql will not reset the outsystems internal tables. Messing with the outsystems tables isn't recomended by outsystems support, i did some messing and it can work but be sure you really know what you are doing. SQL naming of the tables is limited to 16 chars, and all tables start with USR_ then a 3 letter code for your eSpace and an underscore, so only 8 chars reamin for the entity name, thats why you will get numbered tables soon. If you yourself are already using numbers in names this can be confusing if you look at the sql tables.
Using this qry you can nicely see mappings, it lists all entities for an eSpace including attributes and sql table names :

SELECT
    en.ID
,    es.Name as Espace
,   en.PHYSICAL_TABLE_NAME
,   en.[NAME] as ename
,   '[' + ea.NAME + ']' as aname
--,   ea.TYPE
--,   ea.LENGTH
--,   ea.DECIMALS
--,   ea.is_active
--,   ea.*
FROM
    dbo.ossys_Entity en
inner join dbo.ossys_Espace es
    on en.ESPACE_ID = es.ID
inner join dbo.ossys_Entity_Attr ea
    on en.id = ea.ENTITY_ID
       and ea.is_active = 1
where
    left(PHYSICAL_TABLE_NAME,5) =  'osusr'
and es.name = 'pricing'
order by
    es.Name
,    en.[name]
,    ea.order_num

This all applies to outsystems v 4.2 wich we are using

Regards,
Wim

ByTheWay, if you look at some of my previous posts you can see OutSystems really don't like you to mess with system tables.. i do agree with that, it would be the same as when you would mess with sql internal system tables which are read only by default.. but messing sometimes is nessecary when you mess a lot with the Datamodel (some customers really don't know what they want so you keep changing) and OutSystems compiler keeps giving warnings about columns not in the entities but are there in the SQL tables :-( I really would like a cleanup function in the enterprise manager which would clean up system and sql tables of old versions of tables and would remove ol columns. Maybe something for the wish list :-)