sys_dictionary says the column is an integer, yet the value stored is a decimal

[risanchez]

As the title says, how is it possible that the sys_dictionary table has a column defined as an integer; yet when you open one record, the value stored is actually 1.531. According to the Wiki Introduction to Fields - ServiceNow Wiki   the underlying MySQL Data Type for a field of Type Integer is Integer in MySQL.

How can I TRULY map an actual a ServiceNOW data type to a MySQL DB Type in a reliable way since its clear that sys_dictionary is not the way to do it despite the documentation saying otherwise.

Thanks for your help.

[juancarlos_muro]

Hi Ricardo,

The SN Integer data type is mapped to INT in MySQL and actually should not let you enter decimal values.

That is how it is designed to work.

In your case, I would log an Incident with ServiceNow Support to have that investigated providing answer to the next questions:

• Does it happen the same to all Integer fields on your instance or rather it is specific to this table/field?
• How was that table created?
• Was this field always a Integer one or was it changed at some point?

Cheers,

Carlos

[risanchez]

Hello Juan Carlos,

Thanks for taking the time to answer my question. I will look into opening an incident with ServiceNow support; however, regardless of how the table was created initially (I wasn't around when they created it) any database in which you make a schema change like this, switching from an decimal to an int column will either prevent you from doing so due to possible data loss or allow you to do it but data loss will occur. What's for sure is that you cannot store a decimal value in an int column in a database and retrieve it again as a decimal value.

I actually found this problem while I was testing an ETL program I wrote in which I generate the schema of the staging table by reading the information from sys_dictionary and sys_db_object. All my tests worked perfectly until I found this table where SQLBulkCopier refused to store the data due to the data type mismatch. Sure enough, I made some investigation and encountered this interesting case. It is very frustrating because that means that I cannot reliably generate a schema for a staging table since sys_dictionay can't be trusted.

[juancarlos_muro]

Hi Ricardo,

True thing. I actually tested on my instance before posting, created a test table with a Decimal field, added "1.2345" then switched the field to Integer and after accepting the warning that rounded the value of the existing record to "1".

I think that your issue is exceptional, hence it should be investigated by Support.

Kind regards,

Carlos

[risanchez · Accepted solution]

ServiceNOW support engineers confirmed that our instance has some kind of sys_dictionary corruption problem. They acknowledged that this shouldn't have happened and they will be manually fixing the sys_dictionary table for us.

This is not the only case, by the way. I also found a problem whereby a SOAP request to one of our tables was including 2 previously dropped columns from the table. After further investigation ServiceNOW support engineers discovered that the columns did physically exist in the underlying MySQL table yet the sys_dictionary table didn't have a record of them. Manual intervention from ServiceNOW support is going to be required. - See here for more details of my issue: WSDL generated for a table includes deleted columns