Qlik Replicate: SQLserver data truncation issue caused by precision control

[wei_guo]

Qlik Replicate SQL Server target faces the following truncation error:

Failed sending row to table SS.TABLE1
RetCode: SQL_SUCCESS SqlState: 22001 NativeError: 0 Message: [Microsoft][ODBC Driver 18 for SQL Server]String data, right truncation

Resolution

Increase precision enough for the target SQL Server column. This can be achieved in the Qlik Replicate UI transform tab in table settings, or by modifying it directly in the target DB:

ALTER TABLE [SS].[TABLE1]
ALTER COLUMN [AA] NUMERIC(9,4) NOT NULL;
ALTER TABLE [SS].[TABLE1]
ALTER COLUMN [BB] NUMERIC(12,4) NOT NULL;

Troubleshooting and Cause

Checked column precision control/transfer to target, the target precision is too small, which could not hold the numbers from the source, and causes the error.

==source ibm ==
UUBTCPP AA 19 DECIMAL 5
UUBTCPP BB 20 DECIMAL 8

DECIMAL(p) floating point

When you define a column with the DECIMAL(p) data type, it has a total of p (< = 32) significant digits. DECIMAL(p) has an absolute value range 10^-130 - 10^124.

DECIMAL(5,0): Could store a 5-digit whole number like (12345) or (-99999).
DECIMAL(5,5): Could store a number like 0.12345 or 0.00001

==target sqlserver ==
[AA] [numeric](7, 4) NOT NULL,
[BB] [numeric](8, 4) NOT NULL,

3-digit integer at maximum and 4-digit float --> (7,4)
4-digit integer at maximum and 4-digit float -->(8,4)

For example, the source column AA value=99999 , but the target can only hold a 3-digit integer, which will trigger the truncate mechanism. 

Environment

• Qlik Replicate