Qlik Web Connector FTP/SFTP does not work on a Network path while uploading and downloading the files

• The FTP/SFTP server and Qlik Web Connectors are installed on two different Servers.
• Able to authenticate on Qlik Web Connectors but unable to Upload or Download the files.

The following error occurs when trying to Download or Upload a file:

Environment

• Qlik Web Connectors
• FTP/SFTP Server

Resolution

1. Ensure that the Qlik Web Connectors and the FTP/SFTP server are in the same domain.
2. If Qlik Web Connector is running as a service, check under what windows account it is running (or under what windows account the .exe was started).
3. Login to the machine where the Qlik Web Connector is installed.
4. Ensure that the shared file is accessible via windows file explorer.
5. Run Qlik Web Connector under the same Windows Service Account.

Cause

Qlik Web Connector is running under a windows account that has no access to the shared file.

Ex: Qlik Web Connectors is running as a Service under the Local System account, not the Domain Admin Account.

Internal Investigation ID(s)

QB-18377 

A Qlik Replicate task fails with error:

00002482: 2022-12-01T21:05:43 [SOURCE_UNLOAD ]E: An error occurred unloading dataset: .Proposed_Product__c
[InvalidQueryLocatorFault [ApiFault exceptionCode='INVALID_QUERY_LOCATOR'
exceptionMessage='invalid query locator'
extendedErrorDetails='{[0]}'

Resolution

Environment

Qlik Replicate 

March 14th 2023: You may have upgraded Qlik Sense and located a warning banner in your Management Console stating that this version of PostgreSQL is not supported. If you are running a standalone PostgreSQL database later than 9.6, this message can be disregarded. We are working on resolving the underlying cause. 

In this article, we walk you through the requirements and process of how to upgrade an existing Qlik Sense Repository Database from PostgreSQL 9.6 to PostgreSQL 12.5 (see supported scenarios) or install a brand new Repository based on PostgreSQL 12.5.

We will use the Qlik PostgreSQL Installer (QPI).

For a manual method, see: How to manually upgrade the bundled Qlik Sense PostgreSQL version to 12.5 version 

PostgreSQL 9.6 has reached End of Life Reference: PostgreSQL Versioning Policy.

Index

• Supported Scenarios
• Considerations
• Installing anew Qlik Sense Repository Database using PostgreSQL 12.5
• Qlik PostgreSQL Installer - Download Link
• Upgrading anexisting QRD to PostgreSQL 12.5
• The Upgrade:
• Finalizing the Upgrade/Uninstalling the old Database Service:
• Compatibility with PostgreSQL installers
• Troubleshooting
• Related Content

Supported Scenarios

• Upgrades: Before using the Qlik PostgreSQL Installer, upgrade to a supported version of Qlik Sense Enterprise on Windows. Then use the Qlik PostgreSQL Installer to upgrade PostgreSQL.
  
  We tested and validated Qlik Sense Enterprise on Windows from February 2022 to February 2023. Note that November 2022 and later do not support 9.6, and a warning will be displayed during the Sense upgrade.
  
  
• New installs: The Qlik PostgreSQL Installer supports installing a new Qlik Sense Repository Database cluster on a new server, setting up a new environment or migrating an existing Database to a separate host.

Considerations

• This installer will not detect a standalone PostgreSQL database installed by any other means, other than through an official Qlik installer package, installed on the standard folders in the C drive. 
• The installer itself does not provide a rollback feature. This is described below. 
• The user who runs the installer has sufficient permissions to execute it and install services 
• The user who runs the installer has sufficient permissions to create folders and files in the backup destination 
• The backup destination has sufficient free disk space to dump the existing database  
• The backup destination is not a network path or virtual storage folder. It is recommended the backup is stored on the main drive 
• There will be downtime during this operation, please plan accordingly 

Installing a new Qlik Sense Repository Database using PostgreSQL 12.5

1. Run the Qlik PostgreSQL Installer as an administrator 
   
   
2. Click on Install 
   
   
3. Accept the Qlik Customer Agreement 
   
   
4. Set your Local database settings and click Next. You will use these details to connect other nodes to the same cluster. 
   
   
5. Set your Database superuser password and click Next
   
   
6. Set the database installation folder, standard: C:\Program Files\PostgreSQL\12
   
   
7. Set the database data folder, standard: C:\Program Files\PostgreSQL\12\data
   
   
8. Review your settings and click Install, then click Finish
   
   
9. Start installing Qlik Sense Enterprise Client Managed. Choose Join Cluster option.
   
   The Qlik PostgreSQL Installer has already seeded the databases for you and has created the users and permissions. No further configuration is needed.
   
   
10. The tool will display information on the actions being performed. Once installation is finished, you can close the installer.  
    
    If you are migrating your existing databases to a new host, please remember to reconfigure your nodes to connect to the correct host. How to configure Qlik Sense to use a dedicated PostgreSQL database 
    

Qlik PostgreSQL Installer - Download Link

Download the installer here.

Qlik PostgreSQL installer version 1.2.1 Release Notes

Upgrading an existing QRD to PostgreSQL 12.5

Before using the Qlik PostgreSQL Installer, upgrade to a supported version of Qlik Sense Enterprise on Windows. Then use the Qlik PostgreSQL Installer to upgrade PostgreSQL.

We tested and validated Qlik Sense Enterprise on Windows from February 2022 to February 2023. Note that November 2022 and later do not support 9.6, and a warning will be displayed during the Sense upgrade.

Qlik Sense Enterprise on Windows installations other than in C:\Program Files\Qlik\Sense are currently not supported. 

The Upgrade:

1. Stop all services on rim nodes 
   
   
2. On your Central Node, stop all services except the Qlik Sense Repository Database 
   
   
   
   
3. Run the Qlik PostgreSQL Installer. An existing Database will be detected. 
   
   
   
   
4. Highlight the database and click Upgrade 
   
5. Read and confirm the (a) Installer Instructions as well as the Qlik Customer Agreement, then click (b) Next.
   
   
   
   
6. Provide your existing Database superuser password and click Next.
   
   
7. Define your Database backup path and click Next.
   
   
   
   
8. Define your Install Location (default is prefilled) and click Next.
   
   
   
   
9. Define your database data path (default is prefilled) and click Next.
   
   
    
10. Review all properties and click Upgrade.
    
    
    
    
11. The upgrade is completed. Click Close.
    
    
     
12. Open the Windows Services Console and locate the Qlik Sense Enterprise on Windows services.
    
    You will find that the Qlik Sense Repository Database service has been set to manual. Do not change the startup method. 
    
    
    
    You will also find a new postgresql-x64-12 service. Do not rename this service. 
    
    
    
    
13. Start all services except the Qlik Sense Repository Database service. 
    
    
14. Start all services on your rim nodes.
    
    
15. Validate that all services and nodes are operating as expected.

If the upgrade was unsuccessful and you are missing data in the Qlik Management Console or elsewhere, contact Qlik Support.  

Finalizing the Upgrade/Uninstalling the old Database Service:

No further steps are needed unless you plan to upgrade Qlik Sense Enterprise on Windows to the next major release. If you plan to upgrade to, for example, November 2022, the old Qlik Sense Repository Database service must be removed from the server.

Perform the following steps to delete the Qlik Sense Repository Database Service:

1. Open a command prompt and run the command:
   cd "C:\ProgramData\Package Cache"
2. From there, run:
   c:\ProgramData\Package Cache\dir /s PostgreSQL.msi
3. The folder containing PostgreSQL.msi will be revealed.
4. Right-click on the PostgreSQL.msi file and select uninstall from the menu.

Compatibility with PostgreSQL installers

This version of the upgrade tool is fully compatible with the official PostgreSQL installers from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads.

If you need to upgrade to a higher release than Qlik provides, you can install the same minor PostgreSQL release 12.x.

Troubleshooting

1. If the installation crashes, the server reboots unexpectedly during this process, or there is a power outage, the new database may not be in a serviceable state. Installation/upgrade logs are available in the location of your temporary files, for example:
   
   C:\Users\Username\AppData\Local\Temp\2
   
   A backup of the original database contents is available in your chosen location, or by default in:
   
   C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\backup\9.6
   
   The original database data folder has been renamed to:
   
   C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6_deprecated
   
   
2. Upgrading Qlik Sense after upgrading PostgreSQL with the QPI tool fails with:
   
   This version of Qlik Sense requires a 'SenseServices' database for multi cloud capabilities. Ensure that you have created a 'SenseService' database in your cluster before upgrading. For more information see Installing and configuring PostgreSQL.
   
   See Qlik Sense Upgrade fails with: This version of Qlik Sense requires a _ database for _.
   
   To resolve this, start the postgresql-x64-12 service.

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support. The video in this article was recorded in a earlier version of QPI, some screens might differ a little bit.

Related Content

Qlik PostgreSQL installer version 1.2.1 Release Notes  
Techspert Talks - Upgrading Qlik Sense Repository Service
Backup and Restore Qlik Sense Enterprise documentation
Migrating Like a Boss
Optimizing Performance for Qlik Sense Enterprise
Qlik Sense Enterprise on Windows: How To Upgrade Standalone PostgreSQL
How-to reset forgotten PostgreSQL password in Qlik Sense
How to configure Qlik Sense to use a dedicated PostgreSQL database
Troubleshooting Qlik Sense Upgrades

You may notice fiber loop errors appearing in the Windows, QlikView and Qlik Sense logs.

e.g:

StdServer: Fiber loop (10b74b020) stall detected: last fiber loop iteration started 120917 ms ago.

Fiber loop is an entirely normal error to see and is usually just a sign of a busy server. It may not show any symptoms to the end user.
Fiber-loop stalls indicate a problem with a Windows fiber (a kind of thread) that has taken too long to respond. It shows up in the Windows error logs and the user may experience this a client crash as the session has been closed. If the browser is refreshed then a new session is created.

If the peak memory usage is sufficient to drive the server up towards the upper control limit (High Working Set default is 90%), then one can expect to see some level of degraded performance and unusual failures as the in-engine cache will be mostly drained of memory. The required and recurring re-calculations of results will also use a lot of CPU, and this is also likely the path of explanation for the observed stalls.

Environments:

• QlikView 12.00, 12.10 and 12.20 (November 2017)
• Qlik Sense 3.x , June 2017, September 2017, November 2017, February 2018 and onwards

Cause:

Fiber-loop stalls indicate a problem with a Windows fiber (a kind of thread) that has taken too long to respond.
Generally caused by a busy server that may or may not be oversubscribed (causing peaks in resource usage) and/or by slow network performance particularly when working with large apps stored on network device or share.

Resolution:

It would be more a case of investigating the overall server performance, size of the Apps (if these are very large and stored on a network device) perhaps there are issues with Network speed/performance. 
Any performance issues should be investigated with their local IT team to help reduce the number of fiber loop errors reported.

See What are fiber loop errors in the QlikView server event log? for more details, as well as related content below.
 

Related Content:

• How to start investigating a suspected Qlik Sense Performance issue 

In this article, we detail the 12 steps necessary to successfully configure Qlik Sense Enterprise on Windows after an Active Directory Domain name change or moving Qlik Sense to a new domain.

Depending on how you have configured your platform, not all the steps might be applicable. Before updating any hostname in Qlik Sense, make sure that the original value is using the old domain name. In some case, you may have configured “localhost” or the server name without any reference to the domain so there is no need in these cases to perform any update.

Scenario

In this scenario we are updating a three-node environment running Qlik Sense February 2021:

The domain has been changed from DOMAIN.local to DOMAIN2.local

The servers mentioned below are already part of the new domain name DOMAIN2.local and their Fully Qualified Domain Name (FQDN) is already updated as followed:

• QlikServer1.domain.local to QlikServer1.domain2.local: Central node
• QlikServer2.domain.local to QlikServer2.domain2.local: Rim node
• QlikServer3.domain.local to QlikServer3.domain2.local:  Dedicated Postgres and hosting the Qlik shared folder

All Qlik Sense services have been stopped on every node.

Step 1: Update the Postgres Configuration File

The first step is to update the postgres.conf file to listen on the correct hostname

1. Open the postgres.conf (make sure to make a copy) under
   • C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\<version> (for an environment running the native Qlik Sense Repository Database)
   • C:\Program Files\PostgreSQL\<version>\data (for an environment running a dedicated PostgreSQL database)
2. Search for the parameter listen_addresses and update the hostname value if required
   
   
   
   
3. Save the file, close it and start the Qlik Sense Repository Database or PosgresSQL service depending if you are using the native PostgreSQL or a dedicated instance

Step 2: Backup your Qlik Sense site

Before doing any further changes, it is important to take a backup of your Qlik Sense Platform

• Backing up a Qlik Sense site

Step 3: Rename Server Node in QSR database

Now we update the node names in the database with the new Fully Qualified Domain Name.

1. Connect to the Qlik Sense Database using Installing and Configuring PGAdmin4 to access the PostgreSQL
2. Open the QSR Database and navigate through Schemas -> public ->Tables
3. Right-click on ServerNodeConfiguration -> View/Edit Data -> All Rows
4. Modify the hostname of each node if required
   
   
   
   
5. Save by clicking on the below icon available in the toolbar
   
   
   
   
6. Close PGAdmin

Step 4: Rename User Directory for existing users

1. Connect to the Qlik Sense Database using Installing and Configuring PGAdmin4 to access the PostgreSQL
2. Right-click on the QSR Database and select “Query Tool”
3. Run the following query
   

   UPDATE public."Users"
   SET "UserDirectory" = 'DOMAIN2'
   WHERE "UserDirectory" = 'DOMAIN';​

4. Validate the change by running the following query
   

   SELECT * FROM public."Users"​

   
   The User Directory should be updated to the new domain:
   
   
   
   
5. Close PGAdmin

Step 5: Update Service Cluster configuration

1. On the central node run the program C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe and Connect to Database
   
   
   
   
2. Once connected, click on Service cluster and then OK
3. Update the FQDN with the new domain if required and press Save
   
   
   
   
4. Close QlikSenseUtil.exe

Step 6: Update the repository Connection Strings

1. On the central node run the program C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe
2. Select Connection String Editor and click on Read
3. Update the FQDN with the new domain if required and press Save Value in config file encrypted
   
   
   
   
4. Close QlikSenseUtil.exe
5. Repeat the above steps on every rim node

Step 7: Update the dispatcher services connection strings

Verify if a hostname update is required

1. On the central node, open File Explorer and navigate to C:\Program Files\Qlik\Sense\Licenses and open the appsetting.json
2. Check if the parameter host contains the old domain name. If it does then you need to follow the below steps to update it otherwise you can jump to the next section

Update hostname on all Qlik Sense Dispatcher subservices

1. Search for the file called Configure-Service.ps1
   
   In Qlik Sense February, there are 8 of these files placed in different Qlik Sense Dispatcher sub-services. We need to run each of these files with a specific argument. In the previous or later version of Qlik Sense, the list presented below may need to be adapted.
   
   
2. Run the following command in PowerShell ISE as Administrator
   

   cd "C:\Program Files\Qlik\Sense\NLAppSearch\install"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\AppDistributionService"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\HybridDeploymentService"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\Licenses"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\NotifierService\install"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\MobilityRegistrarService\install"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\PrecedentsService\install"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\DataPrepService\install"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>

3. Repeat the above steps on every node (Including the verification)

Step 8: Update the Qlik Logging Service

If you are using the Qlik Logging Service;

1. Run the following command in Command Prompt:
   

   cd "C:\Program Files\Qlik\Sense\Logging"
   Qlik.Logging.Service.exe validate​

2. If you get something like:
   
   Failed to validate logging database. Database does not exist or is an invalid version.
   
   Then it probably means that the hostname needs to be updated
   
   
3. If so, run the following command in Command Prompt:
   

   cd "C:\Program Files\Qlik\Sense\Logging"
   Qlik.Logging.Service.exe update -h QlikServer3.domain2.local​

4. Once done, run the validate command again to verify that the connectivity is working
   
   
   
   
5. Repeat the above steps on every rim node

Step 9: Update the service account running the Qlik Sense services

1. On the central node, open the Windows Service Console an update each Qlik Sense services with the new domain (DO NOT START THE SERVICE AT THIS POINT)
   
   
   
   
2. Repeat the above step on every rim node
   
   

Step 10: Update the host.cfg and remove the certificate

1. On the central node login as the service account
2. Backup the Qlik Sense certificate following Backing up certificates
3. Remove the certificate you have previous backed up from the MMC
4. Make a copy of %ProgramData%\Qlik\Sense\Host.cfg and rename the copy to Host.cfg.old
   • Host.cfg contains the hostname encoded in base64. You can generate this string for the new hostname using a site such as https://www.base64encode.org/ (You may want to decode the original string to see if it contains the old domain name.
5. Open Host.cfg and replace the content with the new encoded hostname.
6. Run Windows Command Prompt as Administrator and execute the following command:
   

   "C:\Program Files\Qlik\Sense\Repository\Repository.exe" -bootstrap -iscentral -restorehostname​

7. In parallel start the Qlik Sense Dispatcher services
8. When the command has completed successfully you should see the following message in command prompt:
   
   Bootstrap mode has terminated. Press ENTER to exit..
   
   
9. Start every Qlik Sense services on the central node and try to access the QMC with https://localhost/qmc

Step 11: Distribute the certificate on the rim node(s)

1. On the rim node(s) login as the service account
2. Backup the Qlik Sense certificate following Backing up certificates
3. Remove the certificate you have previous backed up from the MMC
4. Start the Qlik Sense services
5. On the central node, open the QMC and go to Nodes
6. After a few minutes the rim node should have the status The certificates has not been installed
   
   
   
   
7. Click on Redistribute and follow the instructions to redistribute the certificates on the rim node(s)
8. After a few minutes, the services should be running
   
   

Step 12: Additional updates

User Directory Connector

If you are syncing your users with a User Directory Connector you will need to change the LDAP path to point to the new domain.

1. In the QMC -> User Directory Connector
2. Edit your existing User Directory Connector, check the LDAP Path and update if necessary
   
   

Monitoring Application

You will need to update the monitoring apps and their associated data connection to use the new Fully Qualified Domain Name

• Configuring the Monitoring apps

Data Connections password

• Part of the steps followed earlier, the certificates had to be recreated. As a result, the passwords for the existing data connection needs to be re-typed and saved for all Data Connections and User Directory Connectors that include password information.

Security Rules and Licenses rules

It is possible that you have created rules based on the User Directory. As you have changed the domain name, the user directory has also changed in Qlik Sense so you will need to update those to reflect that.

Environment

• Qlik Sense Enterprise on Windows 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

The following error is seen when attempting a full load:

ORA-26086: direct path does not support triggers

A direct path operation is being attempted on a table which has enabled triggers.

Resolution:

Either disable the triggers on the table or disable Direct Path in the target endpoint. You can disable this in the Advanced tab of the target endpoint. 

See Limitation (Using Oracle as a target) for more information.

Environment:

Qlik Replicate 

A Qlik Replicate tasks fail with the error below:

[DATA_STRUCTURE ]E: SQLite general error. Code <13>, Message <database or disk is full>. [1000506] (at_sqlite.c:475)

Resolution

Follow the options below to resolve the issue:

Option 1:

Free disk space or increasing disk quota. 

Option 2:

Perform a full reload on all of your tasks. This would automatically delete the tswap files and free up space.

Option 3:

Change the location of the Qlik Replicate data directory. See Qlik Replicate Moving the Data Directory on the same server to a larger drive.

Cause

This error usually indicates Replicate has no space to write to due to high tswap files being generated. See Replicate Sorter Files.

Environment

Qlik Replicate 

Qlik Replicate is actively refusing the connection and Qlik Replicate does not start. This may happen after a Windows Update applied changes to a local group policy and a server restart. 

After the reboot, the Qlik Replicate services fail to start and the following error can be found in task logs:

System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:3552

Resolution

1. Verify if the port used is not being blocked by an updated group policy on the virtual machine. The port in this case is 3552.
2. Create an inbound firewall rule allowing the port on the Qlik VM. 
3. Start the Qlik Replicate services and run the tasks. 

Environment:

Qlik Replicate 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

A PostgreSQL database has caused a disk to fill up or a storage limit to be reached. Services have been stopped.

Upon further investigation, it was detected that an inactive replication slot for a Postgresql database has filled up with lag data. This lag exists because the replication slot is still being filled but not consumed. 

Resolution:

Upgrade to a Qlik Replicate version at or above version 2022.5.

If an upgrade is not yet possible, add the retryStartRepSlotActive and timeoutBetweenStartRepSlotRetry internal parameters as a workaround. This value indicates how frequently Qlik Replicate should attempt to start a replication slot before giving up. By default, the value is set to 5. 

For instructions on how to add internal parameters, see Qlik Replicate: How to set Internal Parameters and what are they for? 

1. Stop the task
2. Set the following parameters:
   
   retryStartRepSlotActive (begin with a value of 10)
   timeoutBetweenStartRepSlotRetry (begin with a value of 15)
   
   
3. Resume the task.

A Qlik Replicate task moving data directly from Salesforce Incremental + SOAP endpoint is not writing data to Snowflake Target. All changes are being written/saved to disk and not applied to the target.

An error to watch out for:

Failed to get data in table 'Event'. Error message: null, cause: null

Resolution

Error message: null, cause: null 

This is connection error on Soap API request, most probably due to timeout, since other requests works fine and there are no network issues.

Soap API is good for small amount of data, but for big results, bulk is much faster and has longer timeouts.

As per Salesforce documentation:

query() and queryMore() Batch size limits The maximum batch size is 2,000 records, but this number is only a suggestion. To maximize performance, the requested batch size isn’t necessarily the actual batch size. Salesforce Web Service Connector (WSC) clients can set the batch size by calling setQueryOptions() on the connection object. C# client applications can change the batch size in the QueryOptions portion of the SOAP header before invoking the query() call.

If the SOQL statement selects two or more custom fields of type long text, the batch size can’t be greater than 200 records. This limit prevents large SOAP messages from being returned.

Environment

Qlik Replicate 

Scenario:

In SAP, the extractor is used by two systems: BW and Qlik. To test if that was a problem, a new system for an extractor to be used exclusively by Qlik was created. The BW system did not use Qlik's system for the extractor.

Doing this, the extraction worked.

On the other hand, for full and delta loads, this raises the question: If you utilise BW, can Qlik Replicate do full and delta loads in a standard way (without having to create Z extractor)?

Errors and Warnings to watch for:

Resolution

There is a known limitation on this, as the extractor should be run by Qlik Replicate only. If you use BW and Qlik at the same time, there is a chance of missing records unless they are not crossing each other. 

Environment

Qlik Replicate 

A task which has been running okay for months started to build latency. 

Error:

22007 NativeError: 100040 Message: Date '0000-00-00' is not recognized

Resolution

This was due to a wrong date. We found an expression for the the bad date from the Source. 

Steps to resolve the issue:

1. Open the target table from Replicate GUI
2. Select "Transform" from the left menu
3. For all the dates you see on the right side click on the "fx" and paste the below expression on the expression builder.
   
   
   
   
4. Paste the below expression in Build Expression window on the right side.
   

   CASE
   WHEN strftime('%Y-%m-%d', $ModifiedDate) IS NULL THEN
   '0001-01-01'
   ELSE
   $ModifiedDate
   END​

   
   

5. You can test it by clicking on parse expression and test expression. If the value, you entered is a valid date then the output should be the valid date. If the value, you entered is null you should see '0001-01-01' like above.

Environment

Qlik Replicate 

The Qlik Replicate Server Service may be run by a dedicated service account.

To change the password:

1. Stop all running tasks from either the Qlik Replicate UI or the Qlik Enterprise Manager
2. If the Qlik Enterprise Manager is used to monitor Qlik Replicate, open the Qlik Enterprise Manager and go to Servers and stop monitoring the Qlik Replicate Server.
3. On the Qlik Replicate server operating system:
   1. Open the Windows Services console
   2. Look for the Qlik Replicate Server service
   3. Right-Click and open Properties 
   4. In the Log on tab, change the account information (password)
4. Update the new password in all connections where you used the identical user. Note: depending on the endpoint it is best to use passwords that do not include '['or ']'.
5. Restart the Qlik Replicate Server Service with the new password.
6. If the Qlik Enterprise Manager is used to monitor Qlik Replicate, open the Qlik Enterprise Manager and go to Servers, then open your Qlik Replicate server and change the password in its connection. 
7. Before resuming any tasks, open the task configuration and open the endpoint to test the connection. Update the passwords where needed. 
8. Resume all tasks. 

Environment

• Qlik Replicate 7.0 and above 

Scenario:

Data is being loaded from Azure SQL Managed Instance (MS-CDC) to Kafka using Qlik Replication. Whenever connectivity issues occur, the task is run using Advanced Run Options (Point in Time).

Identical records are loading (Duplicate Records) multiple times. To track data integrity, the header AR_H_COMMIT_TIMESTAMP was used and the AR_H_COMMIT_TIMESTAMP is higher on the 1st committed than the second one.

When restarting the task back in time, the header information for specific changes is different every time the task is run. For example, when a change is captured the header_timestamp may be '2023-02-22 18:58:56.221000'. If the task is resumed back in time before the change the header_timestamp may be '2023-02-22 18:58:56.314000' for the exact same change.

All of the header column values are different.

Resolution

The header timestamp is not an exact value on the change.

In the SQL Server, the transaction timestamp is approximate and based on the last commit that happened in the database. Because of the way that changes are captured in MS-CDC, the header information is not part of the change record and is read from a separate table and since it is not exact you can end up with different header values.

Environment

Qlik Replicate 

The following warning is logged while replicating a view from a Postgres source endpoint. This causes the NCLOB/String columns to be NULL on the target.

[SOURCE_UNLOAD ]V: Value for column 'XXXXX' was truncated. bind type: -8, data len: 10 (10, after truncation: -2), bind len: 0, statement: XXXXXX

Resolution

1. Set the Limited LOB size to exceed the max data volume on the source (default 8 KB)
   
   
   
   Note: 64KB is an example. The actual value depends on the column size on the source. 
   
   
2. Change the column data type:
   1. Table Settings
   2. Transform
   3. Change from NCLOB to WSTRING (64000) and set the data length to the same as the max LOB column length above.
3. Add the following internal parameter on your source Postgres: 
   
   unboundedVarcharMaxSizeODBC
   Value: Set it to max data length provided on the table column.
   
   
   
   For more information on how to set Internal Parameters, see Qlik Replicate: How to set Internal Parameters and what are they for? 
   
   
4. Run the task. 

Environment

Qlik Replicate 

Qlik Replicate tasks with an SQL endpoint fail with:

[SQL Server]Login failed

The behavior began after an upgrade to Qlik Replicate version 2022.11 or later.

Resolution

In the Qlik Replicate UI:

1. Open the SQL endpoint and test the connection
2. If the test fails, verify if the credentials are using Windows authentication
3. With an upgrade to Qlik Replicate 2022.11 (and later), the Qlik Replicate service name was change from Attunity Replicate server to Qlik Replicate Server. See Service name change from Attunity Replicate Server to Qlik Replicate Server 
   
   The default service is run as local system account, which will not have access to your SQL endpoint. 
   
   Change the service back to run with the account that is able to login to the SQL server.
   
4. Test the connection/run the task.

See Working with Windows authentication | Qlik Replicate Help for details.

Cause 

With an upgrade to Qlik Replicate 2022.11 (and later), the Qlik Replicate service name was change from Attunity Replicate server to Qlik Replicate Server. See Service name change from Attunity Replicate Server to Qlik Replicate Server 

The default service is run as local system account, which will not have access to your SQL endpoint. 

Environment

Qlik Replicate 2022.11 and later (upgrade)

Qlik Replicate was upgraded to 2022.11. The Qlik Replicate UI service fails to start post-upgrade. 

Environment

Replicate version 2022.11 

Resolution

Review the repuictl.log stored in ~\replicate\data\logs and look for the following message:

Repository 'Global' or one of its ingredients are out of date, please run the following command to upgrade the repository: RepUiCtl.exe repository upgrade --repository <repository_full_path>

After verifying the error:

1. Open a command prompt as Administrator
2. Change directory to ~\replicate\data\bin
3. From bin run: 
   
   RepUiCtl.exe repository upgrade --repository "d:\Program Files\Attunity\Replicate\data\GlobalRepo.sqlite"
   
   In our example, our install folder is on D:\. Modify this accordingly. 
   
   
4. You will receive a confirmation:
   
   Repository "GlobalRepo.sqlite" has been successfully upgraded.
   Replicate Control Program completed successfully.
   
   
5. Start the Qlik Replicate UI Server Service.

Cause

the "GlobalRepo.sqlite" file did not get update during the upgrade process.

Qlik Catalog container won't start due to the error:

Error: error creating container storage: the container name "catalog-pod-engine" is already in use by "xxxxxx". You have to remove that container to be able to reuse that name: that name is already in use.

Resolution

1. Shutdown the containers with:
   
    ./shutdown_qlikContainers.sh 
   
   
2. Login as the qdc user and remove the podman container in question:
   
   podman rm -f catalog-pod-engine
   podman rm -f catalog-pod-licenses
   
   
3. Verify that no other podman containers are running with:
   
   podman ps -a
   
   
4. Launch the containers again with:
   
   ./launch_qlikContainers.sh

Cause 

The container already exists.

Environment

Qlik Catalog 

Qlik Replicate logs the following warning:

]W: MS-REPLICATION is not enabled for table 'dbo.YourTableName'. Therefore, UPDATE changes to it will not be captured. If you want UPDATE changes to be captured, either define a Primary Key for the table (if missing) or enable Microsoft CDC instead.

However, the table has a Primary Key and exists in an MS-Replication Publication not created by Qlik Replicate.

Resolution

1. From the SQL Server Management Studio (SSMS) login to the SQL database that Qlik Replicate is using for the source endpoint.
2. Check the Replication/Publication that the table(Article) uses, and verify that:
   
   immediate_sync is set to 'true'
   and 
   allow_anonymous is set to 'true' 
   
   
3. If not true, run the below script to update the pulications.
   
   Note: The immediate_sync option needs to be enabled prior to being able to update the allow_anonymous setting.
   

   USE DBName
   
   DECLARE @publication AS sysname
   SET @publication = N'ProdArticlesNoSubs'
   
   USE DBName
   EXEC sp_changepublication
   @publication = @publication,
   @property = N'immediate_sync',
   @value = true
   GO
   
   USE DBName
   DECLARE @publication AS sysname
   SET @publication = N'ProdArticlesNoSubs'
   
   USE DBName
   EXEC sp_changepublication
   @publication = @publication,
   @property = N'allow_anonymous',
   @value = true
   GO​

4. After making the change to the publications, stopped the tasks and then resumed them.

Cause

When the Publication is set for immediate_sync is set to 'false' and allow_anonymous is set to 'false'. This causes the table that being replicated not to register to the sys.table.

Environment

Qlik Replicate  2021 and later

You are seeing a large number of tasks failing. 

When checking your QlikView Distribution Service logs you can see issues to open files.

Example: 

Error: Cannot open file: '\\qlikview.domain.local\Qlikview\QlikViewStorage\Reporting\QVD\2021\xyz.qvd' (System error: Failed to open file in write mode for file: \\qlikview.domain.local\Qlikview\QlikViewStorage\Reporting\QVD\2021\xyz.qvd)

When checking for the QVD on its network location you can observe a size at 0 KB

Environment

QlikView April 2020 SR3 (12.50)

Resolution

To troubleshoot this issue please follow these steps: 

1. Make sure you do not have any security software scanning the QlikView folders or processes 
   
   See QlikView Folder And Files To Exclude From Anti-Virus Scanning
   
   To be sure no other software is locking the QlikView files you can also monitor the file access following the steps from this article:  File Access and Process Monitoring - How to find locked files and the processes locking them
   
   If you find any other 3rd party tool locking QlikView files, please contact your Internal IT team in charge of fixing this. 
   
   
2. Check your file server Disk performance
   
   To save a data collection of  your storage performance you could use Performance Monitor for this: How to log CPU, Disk, and memory usage with Microsoft Performance Monitor on a windows 2012 Server (PerfMon)
   
   You can also review the actual performance live in the Resource Monitor:
   
   
   
   A good indicator to check is the Disk Queue Length. A good-performing disk should show a value as close to 0 as possible.
   
   In this example here we can see a very high Queue Length, demonstrating a general bad performance of the disk, which ended up preventing Qlikview to open files in a timely manner.
   
   See QlikView and its backend File Share System.
   
   If you are facing this problem, please contact your Internal IT team in charge of Storage performance.
   
   Also, make sure you are following best practices documented in Scaling QlikView Publisher 

Cause 

• 3rd party tool locking QlikView files
• Degraded disk performance

Related Content

Scaling QlikView Publisher