Blocks when starting the Attunity task to source MS SQL Server 2022

[Orlandis]

Hello.
We use data replication using Qlik Attunity, when data is taken from SQL Server 2019, everything was fine. Then, after updating SQL Server to version 2022, difficulties began. Multiple errors and locks began when the task was started after stopping, the task was not deleted or updated, the task was simply suspended and started.

The errors look like this

Stream component 'st_0_BPMSQL_BPM' terminated
Stream component failed at subtask 0, component st_0_BPMSQL_BPM 
Error executing source loop
Endpoint is disconnected
Failure in starting (with TXN consistency...)
Failure validating initial LSN 'anywhere existance'
mssql_lsn_anywhere_validator(...)Failed in looking at Active TLOG
lsn_exists(...)Failed in statement creation for looking at Active TLOG

Failure in approving table full logging setup for dbo.AccountInsuranceProgram

mssql_approve_table_full_logging_setup(...) failed in checking PK presence for table identified by ID '1882736243'

The blockages look like this and there are a lot of them (see screenshot)

Please help me solve the problem, thank you

[Dana_Baldwin]

Hi @Orlandis 

By default, Qlik Replicate uses MS-Replication to tell SQL Server to write the changes to the transaction log, and creates a publication for the tables in the task and adds those tables to the publication. Tables need to have a PK to be added to the publication. The errors noted suggest the tables in the task do not - but that is surprising if the only thing that changed was upgrading SQL Server.

1. Please provide the full version number. If it is an older version, it may not be certified with SQL Server 2022.

2. Are all the pre-requisites in place for using SQL Server as a source? Permissions, MS-Replication enabled, etc.?

3. Were there any other changes in conjunction with the SQL Server upgrade? Database backup settings changes, etc.?

If you need immediate assistance it may be best to open a Support case.

Thanks,

Dana

[john_wang]

Hello @Orlandis ,

In addition to Dana's comments, it appears that the publication hasn't functioned properly since the SQL Server upgrade. I suggest trying the following steps:

1. Manually remove and re-add the affected tables in the article list to see if that resolves the issue.

2. If the problem persists, you may need to reconfigure the publication. Please note that doing so will require a full data reload afterward.

Regards,

John.

[Orlandis]

Hello.
Thanks for your reply.
This phenomenon occurs in several environments in SQL Server Developer and Enterprise editions that have Qlik Replicate connected, we have several more Standard environments that we have not yet upgraded from 2019 to 2022.

1. The following Enterprise Edition environments have been updated to date - Microsoft SQL Server 2022 (RTM-CU18) (KB5050771) - 16.0.4185.3 (X64) 2025-02-28 18:24:49 Copyright (C) 2022 Microsoft Corporation Enterprise Edition: Core-Based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)
SQL Server Developer Edition - Microsoft SQL Server 2022 (RTM-CU17) (KB5048038) - 16.0.4175.1 (X64) 12/13/2025 ... 2024 09:01:53 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (build 20348: ) (hypervisor)

Click replication version - Qlik Replicate November 2024

ODBC driver version on Click replication server - ODBC Driver 17 for SQL Server or ODBC Driver 18 for SQL Server

2 and 3 - Nothing else has changed, installed the new 2022 and its update

While replication is running there are no problems, if you stop the task and try to start it, this behavior starts when checking tables in existing publications. We tried deleting publications on SQL Server, tried deleting tasks on the Qlik Replicate side, the behavior does not change, the same errors

[Orlandis]

Hello.
Thank you for your reply.
We have followed all your recommendations.

1. We have deleted all publications on the SQL Server side

2. On the Qlik Replicate side, we have also completely deleted the tasks and recreated them

When the Qlik Replicate task is launched, the publication is created on the SQL Server side and the process of adding tables begins, as a result, Qlik Replicate starts the process of adding tables and goes into sleeping mode, while in parallel the process of checking the primary key for the table is launched, which is blocked by the first process, as a result, it stops working due to a timeout

In the process of starting the Qlik Replicate task, tables are added to the publication, and the process is accompanied by these locks and a task crash, we start the task again and it moves on, so with a task in which there are 97 tables, the task can crash many times and the start process lasts about one hour, as a result, the publication starts and then seems to continue working, whether there are problems after the task has risen with difficulty, we have not yet studied.

The problem is that this behavior is on all updated SQL Servers and we cannot yet understand what to do with it

[john_wang]

Hello @Orlandis ,

Thanks for the update.

Both the SQL Server version/edition and Qlik Replicate version are good. The task should NOT crash in runtime, or during the stop/resume etc.

Would you please open a support ticket with below information:

1- Set source_unload/source_capture to Verbose, reproduce the behavior

2- Download the Diagnostics Package

3- Decrypt the verbose task log files

4- if Replicate runs on Windows, please check the Windows Event viewer to see if any helpful information for the crash

5- Please check SQL Server side log to see if any clue.

Regards,

John.

[Orlandis]

We have caught another observation, when the task is launched, a script is generated to check for the presence of tables in the publication and add them to the publication, this script is generated large for all tables at once, this process is launched and falls asleep waiting for the completion of the process of checking for the presence of the primary key, which cannot work, since it is blocked by the first process. As a result, the process of adding tables falls into Abort state and after that several processes of adding tables are launched, but one by one, and not in a batch as the first time, these several tables are added to the publication and at some point the process of adding tables comes again with a large number and again falls into an Abort state, and this happens until all the tables are added to the publication.

[Dana_Baldwin]

Hi @Orlandis 

Is there anything in the SQL Server logs pointing to why the queries to add articles to the publication fails?

As @john_wang noted, for the fastest response a support case is recommended with the items attached that he mentioned.

Thanks,

Dana

[IgorM · Accepted solution]

Hello, community

The described issue has been resolved by installing hotfix 2024.11.0.514

Best Regards

[john_wang]

Thank you so much for your outstanding support! @IgorM