How do I fix the following error that appears when running an installation because I’ve changed the datatype of a certain field?
It looks like you changed a field with a text type to a numeric type, but one of the records in the database has a non-numeric value for that field. You need to add data conversion to the update to fix that. Data conversion can be added in deployment, or (preferably) in NNetwork by adding it to the relevant revision of the product issue that implements the change.
The simplest way is probably to simply remove all non-numeric values.
In case you are converting to a decimal value, something like this should do the trick:
UPDATE [yourtable] SET [yourfield]=NULL WHERE NOT(ISNUMERIC([yourfield])=1) AND [yourfield] IS NOT NULL
In case of converting to integer, you also need to remove values with decimals, for example like this:
UPDATE [yourtable] SET [yourfield]=NULL WHERE NOT(ISNUMERIC([yourfield]+'.e0')=1) AND [yourfield] IS NOT NULL
It depends on your use case whether a simple conversion like this is sufficient. Of course, we recommend validating the data conversion in an acceptance environment with a copy of the production data first, to prevent data loss.
In this specific situation, there was still an additional query needed, because of this issue: sql server - SQL IsNumeric Returns True but SQL Reports 'Conversion Failed' - Stack Overflow
In a field that was converted from VARCHAR to FLOAT/DECIMAL, the existing value was 400,00 - with a comma.
Apparently it depends on the locale how it’s returned but:
- A string value with a comma as decimal separator, is considered IsNumeric, but cannot be converted to float/decimal
- A string value with a dot as decimal separator, is considered IsNumeric, but can be converted to float/decimal
We solved it by adding this specific additional line:
UPDATE [yourtable] SET [yourfield]=CAST(REPLACE([yourfield], ',', '.') AS FLOAT) WHERE [yourfield] IS NOT NULL
For more clarification:
The error is:
"Protocol error InternalServerError while calling handler file: Internal Server Error\r\nDetails:\r\nSystem.Exception: Running db_9165_r686_to_r687.sql failed ---> System.Exception: Error on line 185: Error converting data type varchar to numeric.
It shows the model number and version update. In this case, it’s model M9165, updating from 686 to 687, that has this error.
In updates than span multiple versions (which happens often in the 2nd and further stages in DTAP), this helps you to identify which scripts is throwing the error.