Errors publishing an eSpace, after changing an entity attribute datatype

Errors publishing an eSpace, after changing an entity attribute datatype

  
When changing an attribute datatype two types of error may happen, when we try to publish the eSpace:

  1. Compile time errors, where a descriptive message is displayed stating that the conversion is not possible. The compilers checks for compatibility between the actual database column datatype and the new definition in the eSpace.
    Eg: trying to change a Binary Data attribute to some other type.
  2. Publish time errors, where the conversion is possible in theory, but depends in actual database values. When this happens, the error returned from the Database Server is displayed, along with the SQL statement that lead to the error.
    Eg: converting a Text(50) to Integer or to Boolean may fail depending on the actual data. Sample of error received:
    Unable to upgrade database schema.
    Error trying to change database field 'osusr_255_TestResult.RESULT' type (New Def: BIT; DB Def: VARCHAR(50)).
    Syntax error converting the varchar value 'False' to a column of data type bit.
    The statement has been terminated.
    SQL: alter table dbo.[osusr_255_TestResult] alter column [RESULT] BIT NULL;


What is the solution to this problem?

How can I publish my espace when I have the need to change the type of a filed?
In most cases, the platform will handle all the database changes to you, including changing the attribute's datatypes.

In the cases described above (1. and 2.), manual intervention will be needed:

1. Incompatibility between database column datatype and Entity attribute datatype. Compile time error.
I present here one solution to allow changing the datatype. Others may exist.

In sql server query analyzer, you may rename the original column (use SP_RENAME) - this serves to save your data. Then recompile/publish your eSpace - this will create the column with the correct datatype. Now, you must copy from the renamed column to the destination one, applying some transformation of data. This transformation may be done in sql server query analyzer (through script, stored procedures, etc.) or via other means (SS inclusivé). After that, you may manually drop the column in database.

As an example, imagine you want to change Gif_Image attribute from Binary Data to a Text(N), that will have its data in text BASE64 instead of binary. This change is not supported automatically. You may rename the column to Gif_Image_Aux and then apply the steps explained above.

TIP: If you use enterprise manager, to change the column type, and look at/use/copy&paste the script that it generates might help you.

If the data in that column is not needed at all, you may drop the column in sql server instead of renaming it. This may happen for example, if the application is in development, with no real data at all.

2. Compatibility in datatypes, but Incompatibility with actual database data. Publish time error.

This case is simpler. You may just change the data in the database, in sql server query analyzer or even is Service Studio, and then recompile/publish.

As an example, imagine you want to change Is_Ok attribute from Text(10) to Boolean. This would work with no problem if the values in the database were '1' / '0'. But if, instead, you have 'False' / 'True' or 'Yes'/'No' this conversion could not be done automatically. You might use two UPDATE sql statements to change the underlying data (to '1' and '0') and then recompile/publish.

I have encountered this problem a few times, In my case it did not matter if data in the table was lost so I just deleted the whole table in enterprise manager and recompiled my espace so the table would just be created instead of adjusted.
There is a simpler way if you do not have database permissions and the application data is not relevant at all.
You can simply go to Edit eSpace page in Service Center, disable all eSpace tenants and then click Delete eSpace button.
You can then publish the file again.
I'm a new OutSystems user and I also have the same problem and needed to delete my eSpace, but on the edit eSpace screen i don't see any Delete button.
I already deactivated the tenant is there anything I'm missing?
Hi Gonçalo,

to be able to delete eSpaces you must be an administrator. That's why you don't see a delete button on the details of your eSpace.

If you need help deleting the eSpace, please send us the name and we will delete it.

Best regards,
Cláudia Macedo