Issue with changing SQL Data type of feild in DBDICT

Hi Experts,


I am trying to achieve something very simple. There is this field on my knownerror table. It’s VARCHAR(60) so it gets filled up very soon and client wants the space to be extended. The following error is received while filling up the field and data is truncated:

“Length (145 bytes) of data for field aat.rejection.reason in knownerror exceeds max (60 bytes), truncated (se.base.method,update.record)”


I changed the SQL data type to VARCHAR (180) but even that’s not enough. So I thought of changing data type to text so that there is no space issue, just like the description and other fields have. When I changed the SQL data type to TEXT, I got the following error:

“Changing SQL data type from 'VARCHAR(60)' to 'TEXT' for column 'AAT_REJECTION_REASON' in table 'KNOWNERRORM1' is not supported. Update cancelled.”

I read many articles on this error but none of them seemed to provide solution to my problem. I noticed that the other TEXT  fields have been defined as an array and then as TEXT at level2. (screenshot attached).

Can anyone suggest anything so that users get enough space for this field.

Thank You!

Truncation error on KE.docx
  • Verified Answer

    If I remember correct, it's not possible to change the data type if the field contains data.


    The approach I have used is :

    1) Create a new field to the table you want, make sure the data type is what you want

    2) Do the mass update, copy the stuff you have in a "old" field to new field

    3) Change the name of old field to for example "xxx.notinuse", do the same to SQL field name

    4) Rename the new field to match the old field's name to complete the "field swap"

    (I had some issues with svc.options field in Incidents table, so I had to do the swap.)


    But in your case, I think it's also possible to expand the VARCHAR(180) up to VARCHAR(500), for example.

  • The easiest way to do it is to make your changes in the database backend, and then update your dbdict to match.


    Because, as you found and as the previous poster explained, you can't change from VARCHAR(x) to TEXT.  Neither HPSM nor the database will allow it.


    So you have to cheat.


    First, in the dbdict, in the SQL Table column for that field, change the m1 to n1,  In the  SQL Tables tab, create an alias like the following:

    Alias: n1

    Name: nulltable

    leave Type, Table Keys and Table Options blank.


    Save your changes, log out of HPSM.


    Log into the underlying database.  You can try to change the name of the column (like AAT_REJECTION_REASON_OLD), but if it doesn't let you, then create a new field (AAT_REJECTION_REASON_OLD, type VARCHAR(180)) and temporarily copy all the data that's in your current field into the new field.  

    Then, create a new field (AAT_REJECTION_REASON, type TEXT) and copy all the data from the AAT_REJECTION_REASON_OLD into this new field.  Once the data has been copied, you can delete the _OLD field.


    Now, back in HPSM, re-map the data type from VARCHAR(160) to TEXT, but keep the mapping to your n1 table.  Save your changes and log out.


    Log back in and change the mapping back to m1, and you should be good to go.


    Here's what happens - 

    When you try to change the data type in the dbdict to TEXT, the system issues the commands to the underlying database to change the data type.  That's not allowed (can't change from VARCHAR to TEXT) so the database gives you an error, so HPSM gives you an error.  When you map to the nulltable, you basically trick HPSM into not caring about the response from the database.  (I'm not sure if it issues commands to the table that doesn't exist, or if it skips issuing commands because it doesn't know the database type.  Either way, you avoid getting the error when you change the data type).  Then, once you go through the hoops to create the new field in the database of the correct type, when you change the mapping in the dbdict, HPSM doesn't issue any command; it just checks the data type in the dbdict to the data type in the database.  Since they match (TEXT), it gives you no errors.


    I prefer to do it through the database, because there's no way to delete a field in HPSM dbdict.  If you create a new field (the att.rejection.reason.old) you're stuck with that field forever.  By doing it in the underlying database, you can delete the field after you're done with it, and HPSM is none the wiser.


  • Hi Kelalek,

    I did try out both the options suggested by you in my test environment and both worked really fine. However, the easiest one is too change the feild type from VARCHAR(60) to VARCHAR(500). The only problem with this solution is, it doesnt support carriage return. Let me see which solution is acccepted by my Cleint.

    Thank you very much!

  • Hi Jacob, 

    Thank you for the perfect solution and the explanation :)

  • Hi Jacob, 

    Thank you for the perfect solution and the explanation :)

  • Hi,

    I am using oracle database and want to set max varchar limit. Can you help to update?