topic Re: How to run SELECT CHANGE_TRACKING_CURRENT_VERSION() in data load editor when CT is enabled in MSSQL data source in Data Movement & Streaming

How to run SELECT CHANGE_TRACKING_CURRENT_VERSION() in data load editor when CT is enabled in MSSQL data source

EspenH — Wed, 19 Mar 2025 21:48:59 GMT

We are using Qlik Colud and have a MS SQL data source where MS SQL native Change Tracking (CT) is enabled on the data base and the tables we need to load.

To utilize the CT functionality (and only load the changed data) we need to run some CT functions. To get the current change tracking version (in MS SQL management studio) you run only SELECT CHANGE_TRACKING_CURRENT_VERSION() which returns the last change tracking version.

We need get this value in Qlik data load editor, but struggle to make this work.

In data load editor we have tried:

LIB CONNECT TO 'DatabaseConnectionName';

Change_Tracking_Version:
LOAD '(no column name)';

SQL
SELECT CHANGE_TRACKING_CURRENT_VERSION();

This returns a table with one line with value '(no column name)'

Do any of you experts know how to do this? Please advise.

Re: How to run SELECT CHANGE_TRACKING_CURRENT_VERSION() in data load editor when CT is enabled in MSSQL data source

EspenH — Wed, 14 Aug 2024 05:05:59 GMT

I found a solution, I had to alias the function.

I changed the script:

LIB CONNECT TO 'DatabaseConnectionName';

Change_Tracking_Version:
LOAD '(no column name)';

SQL
SELECT CHANGE_TRACKING_CURRENT_VERSION() ct;

And now the load editor returns the value.

Re: How to run SELECT CHANGE_TRACKING_CURRENT_VERSION() in data load editor when CT is enabled in MSSQL data source

michele123 — Wed, 14 Aug 2024 06:29:01 GMT

Stored Procedure:
- Create a Stored Procedure: Develop a stored procedure in SQL Server to encapsulate the logic of retrieving the CHANGE_TRACKING_CURRENT_VERSION().
- Call from Qlik: Invoke this stored procedure within your Qlik load script to fetch the version.
Multiple SQL Statements:
- Sequential Execution: Execute two separate SQL statements in your Qlik load script.
- Temporary Table: Store the CHANGE_TRACKING_CURRENT_VERSION() value in a temporary table and join it with your main data query.
Qlik Script Variables:
- Store Value: Use Qlik script variables to store the CHANGE_TRACKING_CURRENT_VERSION() value.
- Leverage in Subsequent Loads: Use the stored value in subsequent load scripts.