Connection issues to SQL Server post upgrade from QR Nov 2021

[MoeE]

Hi,

I thought I'd make a post here first in case anyone knew anything before creating an official support case. We recently upgraded from QR Nov 21 to QR May 24.

We encountered odd issues where the tasks would not work post upgrading from QR Nov 21. The tasks are SQL to Snowflake. The issue is that tasks would not start the full load and this error would appear in the logs after some inspection. This is the error we saw in the task after doing a test SQL to Null target task:

[SOURCE_CAPTURE  ]E:  RetCode: SQL_ERROR  SqlState: 42000 NativeError: 53 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53

RetCode: SQL_ERROR  SqlState: 01000 NativeError: 7412 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]OLE DB provider "SQLNCLI11" for linked server "repl_distributor" returned message "Login timeout expired".

RetCode: SQL_ERROR  SqlState: 01000 NativeError: 7412 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]OLE DB provider "SQLNCLI11" for linked server "repl_distributor" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

It might be worth noting, when we did a normal SQL to Snowflake task, the full load would not progress past 0% and increased logs on source_unload and target load showed no activity at all. It's almost as if the task did nothing until we stopped it. We tried this in two separate tasks with two separate SQL source database (different server).

We used these same SQL source endpoints from the SQL to Snowflake tasks in our SQL to Null target task, and it would display those errors seen above. This seems odd to me.

We ensured all prerequisites are met except creating a publication for this database as only full load is required. Replication is enabled on the server level and we are able to run full loads flawlessly on QR Nov 21 or previous versions. 

I wonder if this is something as simple as deleting the older ODBC drivers after upgrading?

If anyone has seen this issue before please help point us in the right direction. Thank you.

Regards,

Mohammed

[john_wang]

Hello Mohammed, @MoeE 

Thanks for reaching out to Qlik Community!

I'd like suggest to check:

1- How about if you disable the Change Processing

2- How about if you use internal parameter "provider" in source endpoint to make sure Replicate is using the correct version ODBC Driver, for example:

3. How about if you create a 64-bit ODBC DSN and see if it works in Qlik Replicate?

BTW, what's the SQL Server version and platform?

thanks,

John.

[MoeE]

Hi John,

Thanks for the help. Change processing was disabled on a task that had this same issue. We are able to successfully test connect using the 64-bit DSN. The only thing to check now is the provider however I am awaiting the client's response on this. Appreciate the suggestions. Although, the logs do suggest that Microsoft ODBC 18 is in use, do you think some other driver could be in use instead?

[METADATA_MANAGE ]I:  ODBC driver 'ODBC Driver 18 for SQL Server' found as installed 

Regards,

Mohammed

[john_wang]

Hi Mohammed, @MoeE 

Thanks for the update.

Microsoft ODBC Driver 18.3 is certified version in Qlik Replicate 2024.5, it's recommended.

Regards,

John.

[MoeE]

Hi John, 

Thanks. Yep, 18.3.3 is installed and it is 64 bit.

Regards,

Mohammed