Knowledge

[shashi_holla]

This article is to provide more details on the following warnings we see for SOURCE_CAPTURE from SQL Server source endpoint during CDC:

peform_row_lookup(...) cannot apply LOOKUP for '<table_name>' since it does not have a primary key. (sqlserver_log_formatter.c:437)

do_handle_captured_row_reptask(...) failed in composing data for '<table_name>' due to data truncation. (sqlserver_log_processor.c:1449)

do_process_retrieved_row(...) row handler encountered a data error (sqlserver_log_processor.c:3946)

Environment

• Qlik Replicate

Cause

This is because the transaction size being read from transaction logs exceeds the allowable limit and Replicate has to read the data directly from the table. But could not read from table since it doesn't have a primary key.

Resolution

1) Quick fix would be to add Primary key (PK) on the table

2) If option 1 is not possible then alternative solution is to enable MS-CDC on the table and upgrade the environment to May2022 sp02 (2022.5.0.499) version of Replicate which has introduced MS-CDC source endpoint which will read the data directly from CDC tables from SQL Server and not from the transaction logs. This will ensure we read the data in Full and don't have to truncate it because of read size limit.

Note: The existing license for SQL endpoint will work for MS-CDC source as well and no additional license requirement.