Error executing data handler Endpoint is disconnected

[Naga_vivek]

Hi Team,

In one of our replicate task. When loading the full load we are facing the below issue. The full load does not get completed for one of the table. How many times we reload the task it is taking time and throwing the same error

Stream component 'st_1_<target endpoint>' terminated
Stream component failed at subtask 1, component st_1_<target endpoint>
Error executing data handler
Endpoint is disconnected
Execute create Primary Key/Unique Index failed, statement ALTER TABLE <table Name> ADD CONSTRAINT <contraint name> PRIMARY KEY ( <primary key column> )
RetCode: SQL_ERROR SqlState: HYT00 NativeError: 0 Message: [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired
Failed (retcode -1) to execute statement: ALTER TABLE <table Name> ADD CONSTRAINT <contraint name> PRIMARY KEY ( <primary key column> )

Task details and the Qlik details.

---------------------------------------------

We are using Qlik replicate May 2023 version.

Source end Point :

type : Microsoft SQL server (database)

Change processing mode (read changes from): Online Logs Only

Apply TLOG truncation prevention policy every (seconds): 600

Internal parameters:

Ignoretnxvaliditycheck:True

Tlogaccessmode: Tlogonly.

Target end Point :

type : Microsoft SQL server (database)

Can someone help how I can resolve the error . Please let me know if you need any more information.

Thanks ,

N L N Vivek

[john_wang]

Hello @Naga_vivek ,

Thanks for reaching out to Qlik Community!

In general Qlik Replicate creates target table prior to load data, includes ALTER TABLE <table Name> ADD CONSTRAINT <contraint name> PRIMARY KEY operation. Not sure if you enabled Full Load setting "Create primary key or unique index after full load completes" :

Anyway, you may try to execute the ALTER TABLE manually to see how long it take to understand the root cause further.

BTW, maybe an internal parameter helps:

1. Open SQL Server target endpoint
2. Go to the Advanced tab
3. Open Internal Parameters
4. Add a new parameter named executeTimeout
   Set its value to a reasonable value eg 3600 (it's seconds, means 1 hour) if you got this error after 1 hour running.

Good luck,

John.

[Naga_vivek]

hi @john_wang ,

I tried enabling the below setting:

Full Load setting "Create primary key or unique index after full load completes" .

and also tried by adding a new parameter named executeTimeout
setting it with 3600 and 7200 but still I am facing this issue when running fulload for the table.

It is getting stuck while loading the last records for the table. Could you please help what we can do.

Thanks and Regards

N L N Vivek

[john_wang]

Hello @Naga_vivek ,

It's hard to tell if 3600/7200 is good enough or not without proper task log file. Because the value depends on data volume and the SQL Server target database performance, or even depends on data quality. Let's troubleshoot the issue by steps (drop the table first in every step):

1- Manually create the target table and then try to add the PK manually; how long it takes?

2- Let Replicate create the target empty table (by adding a filter, eg "where 1=0"), then try to add the PK manually;

3- Similar with 2 but with part of data by using filter, then try to add the PK manually and record how long the "alter table" operation takes;

4- Similar with 3 but with all data, then try to add the PK manually and record how long the alter table operation takes;

5- Set executeTimeout a larger value than the time in step 4, run the task again.

You may compare task runs with and without setting "Create primary key or unique index after full load completes" option. please take care if there are duplicate records which leads the alter table failed or timeout.

Hope this helps.

John.