topic Re: Failed (retcode -1) to execute statement: 'DROP TABLE in Qlik Replicate

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

Al_gar — Thu, 14 Apr 2022 17:26:44 GMT

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.

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

KellyHobson — Thu, 14 Apr 2022 17:30:52 GMT

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

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

PGN — Thu, 14 Apr 2022 17:54:05 GMT

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.

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

Al_gar — Thu, 14 Apr 2022 18:16:28 GMT

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.

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

lyka — Thu, 14 Apr 2022 18:20:38 GMT

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

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

Heinvandenheuvel — Fri, 15 Apr 2022 13:17:12 GMT

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.

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

lyka — Tue, 26 Apr 2022 21:57:28 GMT

Hello,

Did any of the suggested solution work?

Thanks

Lyka