Qlik Replicate: NULL result in a non-nullable column

[Gerald_U]

A Qlik Replicate task is suspended due to the following error:

Line 142049: 00024400: 2024-12-10T12:15:45:607996 [TARGET_APPLY ]E: RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column [1022502] (ar_odbc_stmt.c:5090)

Resolution

There are different approaches to resolving the issue.

Add a Null Check coalesce transformation with the same default value.

Example:

The column SalePrice decimal(18, 2) default 0 not null was added to a table with existing data.

To fix this, add the transformation below to the column:

coalesce($SalePrice,'0')

Alternatively, rewrite every row of the table in the source database.

Cause 

This is a known issue with SQL servers when a not null column with a default value is added to a table with existing data with null values.

In SQL Server 2012 and later, the metadata is added, but the default value is not added until the column is modified.

SQL Server will store the default value in metadata and use that to retrieve a value for old rows until the modification occurs.

New rows however will get the default value written out to the row immediately.

Environment

• Qlik Replicate
• SQL Server as the source