Qlik tasks failing after installing new ODBC 18 driver.

[desmondchew]

We are replicating from source SQL server 2022 into target Snowflake. We have ODBC 17 and installed ODBC 18 into the server. After installing, the task been running fine. After several hours, however it started to give error.

Failed to prepare get capture list statement 

 select

    SCHEMA_NAME(uid)    as ownerName,

    name              as tableName,

    id                as objectId,

     0 as row_count,

    type as tableType

 from sysobjects

 where

(category=0 or category = 32) -- exclude SYS pretenders

 and OBJECTPROPERTY ( id , 'IsSystemTable' ) =0

 and   name<>N'sysdiagrams'

 and   name not like N'attrep_mscdc_ddl_history'

 and

(

    (( 

 ( type = N'U' ) and SCHEMA_NAME(uid) = N'dbo' and name = N'PAss'

 or 

 ( type = N'U' ) and SCHEMA_NAME(uid) = N'dbo' and name = N'PassThru_ForQlik_Daily'

 ) -- Explicit Inclusion

    or 

    (

       ( (1=0) ) -- Patterned Inclusion

         and not

       ( 

 ( type = N'U' ) and SCHEMA_NAME(uid) LIKE N'%' and name LIKE N'attrep_changes%'

 or 

 ( type = N'U' ) and SCHEMA_NAME(uid) LIKE N'%' and name LIKE N'attrep_apply%'

 or 

 ( type = N'U' ) and SCHEMA_NAME(uid) LIKE N'%' and name LIKE N'attrep_truncation%'

 or 

 ( type = N'U' ) and SCHEMA_NAME(uid) LIKE N'%' and na

Task 'AXReport_AX' failed
Build tables list failed
Cannot get captured tables list
Failed to get the capture list from the endpoint

Cannot access source partitions as the user does not have the VIEW DATABASE STATE permission. The Full Load task will continue, but changes to partitioned tables will not be captured. RetCode: SQL_ERROR SqlState: HYT00 NativeError: 0 Message: [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired. Failed (retcode -1) to execute statement: 'select 1 FROM sys.dm_db_partition_stats'

We did grant the permission
USE DB
GO
GRANT VIEW DATABASE STATE TO produser;
GO

Any idea?

Desmond

[john_wang]

Hello Desmond, @desmondchew 

The error is not related to the ODBC Driver 18. It appears to be a permission issue.

Could you please try running the following query manually using the produser account?

select 1 FROM sys.dm_db_partition_stats;

If the required permissions are in place, the query should return rows.
If not, SQL Server will return an error indicating any additional permissions are required.

thanks,
John.

[desmondchew]

I login as the Qlik user - prod and able to run the query. Doesn't seem like a permission issue.

[john_wang]

Hello Desmond, @desmondchew 

Thanks for the update.

Could you please try running the same test against another table as well? For example:

 select top (1)

    SCHEMA_NAME(uid)  as ownerName,

    name              as tableName,

 from sysobjects;

If the issue persists, I would suggest opening a support ticket and uploading the server log files so that we can investigate further.

thanks,

John.

[desmondchew]

Hi John,

Thank you. We have identified the issue. It is due to a blocking session in the source database. Once we killed that session, replica is able to move.

Desmond