Failed (retcode -1) to execute statement: 'DROP TABLE

[Al_gar]

Today we "Reload Target" on our DEV environment and we started to see the following error message:

00002256: 2022-04-14T12:16:18 [SOURCE_CAPTURE ]T: Entering 'saphana_endpoint_free' (saphana_endpoint_imp.c:551)
00001728: 2022-04-14T12:16:18 [TASK_MANAGER ]I: Task error notification received from subtask 2, thread 1, status 1020414 (replicationtask.c:3433)
00002256: 2022-04-14T12:16:18 [TARGET_LOAD ]E: Failed (retcode -1) to execute statement: 'DROP TABLE [OWNER].[TABLE_NAME]' [1022502] (ar_odbc_stmt.c:4974)
00002256: 2022-04-14T12:16:18 [TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: HYT00 NativeError: 0 Message: [Microsoft][SQL Server Native Client 11.0]Query timeout expired [1022502] (ar_odbc_stmt.c:4981)
00002256: 2022-04-14T12:16:18 [TARGET_LOAD ]E: Failed to drop table 'OWNER'.'TABLE_NAME' [1022506] (sqlserver_endpoint_imp.c:3696)
00002256: 2022-04-14T12:16:18 [TARGET_LOAD ]E: Handling new table 'OWNER'.'TABLE_NAME' failed [1022506] (endpointshell.c:2924)
00002256: 2022-04-14T12:16:18 [TARGET_LOAD ]E: Endpoint is disconnected [1020414] (endpointshell.c:3971)
00002256: 2022-04-14T12:16:18 [TARGET_LOAD ]E: Error executing data handler [1020414] (streamcomponent.c:2001)
00002256: 2022-04-14T12:16:18 [TASK_MANAGER ]E: Stream component failed at subtask 2, component st_2_ENDPOINT_TGT [1020414] (subtask.c:1396)
00002256: 2022-04-14T12:16:18 [TARGET_LOAD ]E: Stream component 'st_2_ENDPOINT_TGT' terminated [1020414] (subtask.c:1565)
00001728: 2022-04-14T12:16:18 [TASK_MANAGER ]I: Subtask #2 ended (replicationtask_util.c:591)
00001728: 2022-04-14T12:16:18 [TASK_MANAGER ]I: Reloading table 4 because subtask #2 finished with error (replicationtask.c:2613)

Source endpoint is HANA and target is MS SQL Server. This was working OK and there has not been any changes on the endpoints configuration or the Replicate task.

[KellyHobson · Accepted solution]

Hey @Al_gar ,

Please see this community post : https://community.qlik.com/t5/Knowledge/Query-timeout-expired/ta-p/1779090

Try increasing the timeout on your SQL Server target.

Best,

Kelly 

[PGN]

Generally, it's been my experience, timeouts on table drops are due to the target table (one to be dropped/reloaded) is in use, i.e., locked, therefore Replicate can't drop the table.  Usually table drops should be instant.

Did you check for blocking on the target server?  The Replicate task command to drop the table will be the SPID being blocked.

[Al_gar]

Kelly,

I set the executeTimeout = 600 and still output the error message. However, the table loaded 15 secs later after Replicate sent out the notification error.  Not sure if increasing again the timeout would be an ideal solution, specially if some of the tables are smaller than 25K records.

Thanks.

[lyka]

Hello,

Increasing the timeout should address the issue. For testing purposes, if you get another error, you can try to run the statement that failed manually on the target db and get the timing. 

Thanks

Lyka

[Heinvandenheuvel]

Hmm, why  are timeouts considered? Everything seems to be happening in the same second (to be verified in the log looking for the last mention from thread "00002256:" just before the published section and/or the last SOURCE_UNLOAD for the table in play

My guess is that the "Endpoint is disconnected" is the cause, not a follow up action. There may be information in the target DB logs as to why there might have been a disconnect. Retry is likely to fix it

Just guessing.

Hein.

[lyka]

Hello,

Did any of the suggested solution work?

Thanks

Lyka