Replicate error during full load from Oracle to Azure

[jamkgrif]

we are working on qlik replicate to migrate data from Oracle source to Azure target. A number of task completed however, two failed. They have access, firewall permissions, server isn't timining out. what could these errors mean?

00002740: 2020-08-26T09:01:17:473547 [TARGET_LOAD ]I: Going to connect to server 'servername.database.windows.net' database 'azure db name' (sqlserver_endpoint_imp.c:1656)

00002740: 2020-08-26T09:02:04:700120 [TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 08001 NativeError: 53 Message: [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53]. [1022502] (ar_odbc_conn.c:361)

00002740: 2020-08-26T09:02:04:700120 [TARGET_LOAD ]E: Cannot connect to SQL Server [1022506] (sqlserver_endpoint_imp.c:1662)

00002740: 2020-08-26T09:02:04:700120 [TASK_MANAGER ]E: Failed while preparing stream component 'oracledb.table name - azure db name'. [1022506] (subtask.c:825)

00002740: 2020-08-26T09:02:04:700120 [TASK_MANAGER ]E: Cannot initialize subtask [1022506] (subtask.c:1269)

00002740: 2020-08-26T09:02:04:700120 [TARGET_LOAD ]E: Stream component 'task name' terminated [1022506] (subtask.c:1537)

00005944: 2020-08-26T09:02:04:917160 [TASK_MANAGER ]I: Subtask #10 ended (replicationtask_util.c:597)

00005944: 2020-08-26T09:02:04:917160 [TASK_MANAGER ]E: Task error notification received from subtask 10, thread 1 [1022506] (replicationtask.c:2706)

00005944: 2020-08-26T09:02:04:957163 [TASK_MANAGER ]W: Task 'task name' encountered a recoverable error (repository.c:5396)

00001368: 2020-08-26T09:03:34:892594 [TARGET_LOAD ]I: Bulk is set to ignore max row size warnings (sqlserver_endpoint_imp.c:1569)

[lyka · Accepted solution]

Hello,

This looks like a SQL issue and not Replicate but i did some research and found a few helpful links -

Have you tried these solutions?

https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-erro...

https://stackoverflow.com/questions/9945409/how-do-i-fix-the-error-named-pipes-provider-error-40-cou...

OR Try this:

1) Create SQL Server Client alias on the Replicate server to force us to use TCP.

Instructions:

The program name is CLICNFG.EXE and is usually under c:\windows\system32

https://sqlandme.com/2011/05/05/create-sql-server-alias-cliconfg-exe/

Enable the TCP/IP on the general tab and then add it in the Alias tab.

If you add an alias to force the connection to use TCP, make sure you set the instance name on the end of server name if using a non-default instance.

Here is a query you can use to find out if named pipes is enabled:

SELECT 'Named Pipes' AS [Protocol], iif(value_data = 1, 'Yes', 'No') AS isEnabled

FROM sys.dm_server_registry

WHERE registry_key LIKE '%np' AND value_name = 'Enabled'

UNION

SELECT 'Shared Memory', iif(value_data = 1, 'Yes', 'No')

FROM sys.dm_server_registry

WHERE registry_key LIKE '%sm' AND value_name = 'Enabled'

UNION

SELECT 'TCP/IP', iif(value_data = 1, 'Yes', 'No')

FROM sys.dm_server_registry

WHERE registry_key LIKE '%tcp' AND value_name = 'Enabled'

Please also check with your network admins  if they have access control list in place that only allows certain authorized programs to make the connection. We have seen cases like that and adding an entry to allow connections to the port helped with the issue.

Thanks,

Lyka

[jamkgrif]

Thank you for your reply. I think we overloaded the system. We previously ran our pipelines in parallel with these errors. once we ran then separately and turned off named pipes it resolved itself.