Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Replication task is throwing below message but the task is not errored out.If any one have idea please let me know how to correct this?
Error Code: 1022502
Error details: Failed to start bulk apply transaction Failed to execute bulk update statement.
RetCode: SQL_ERROR SqlState: 25000 NativeError: 11120 Message: [Snowflake][ODBC] (11120) Outstanding transactions during disconnect.
RetCode: SQL_ERROR SqlState: 08001 NativeError: 390114 Message: Authentication token has expired. The user must authenticate again.
Sashidhar
Hello @Sashidhar ,
In general this message means the snowflake connection expired due to inactivity. By default, the connection lasts 4 hours. From other support ticket I'd like to suggest:
1- Add the below value to the ODBC connection string for the snowflake target endpoint
CLIENT_SESSION_KEEP_ALIVE=true;ABORT_DETACHED_QUERY=true;CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=900;
2- Add following registry keys on the Replication Server
HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/CLIENT_SESSION_KEEP_ALIVE
HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY
HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/ABORT_DETACHED_QUERY
Values are true, 900 and true respectively
3- And did the following to the user
alter user user_name set CLIENT_SESSION_KEEP_ALIVE = TRUE;
alter user user_name set ABORT_DETACHED_QUERY = TRUE;
alter user user_name set CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY = 900;
Some links for your reference:
https://docs.snowflake.net/manuals/sql-reference/parameters.html#client-session-keep-alive
https://docs.snowflake.net/manuals/user-guide/odbc-parameters.html#connection-parameters
Hope this helps.
Regards,
John.
Hello @Sashidhar ,
In general this message means the snowflake connection expired due to inactivity. By default, the connection lasts 4 hours. From other support ticket I'd like to suggest:
1- Add the below value to the ODBC connection string for the snowflake target endpoint
CLIENT_SESSION_KEEP_ALIVE=true;ABORT_DETACHED_QUERY=true;CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=900;
2- Add following registry keys on the Replication Server
HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/CLIENT_SESSION_KEEP_ALIVE
HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY
HKEY_LOCAL_MACHINE/SOFTWARE/Snowflake/Driver/ABORT_DETACHED_QUERY
Values are true, 900 and true respectively
3- And did the following to the user
alter user user_name set CLIENT_SESSION_KEEP_ALIVE = TRUE;
alter user user_name set ABORT_DETACHED_QUERY = TRUE;
alter user user_name set CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY = 900;
Some links for your reference:
https://docs.snowflake.net/manuals/sql-reference/parameters.html#client-session-keep-alive
https://docs.snowflake.net/manuals/user-guide/odbc-parameters.html#connection-parameters
Hope this helps.
Regards,
John.
Shouldn't Replicate do this setting at each session level through the ODBC driver?
Recently I have noticed the same issue in Qlik Compose too.
Previously we were told to set:
CLIENT_SESSION_KEEP_ALIVE=false;
Hi @john_wang ,
We had this error while the task full load was going on. And the table is in error state. It was a single table task.
03190811: 2023-08-18T06:39:56 [TARGET_LOAD ]E: RetCode: SQL_SUCCESS SqlState: 08S01 NativeError: 104 Message: [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x68 [1022502] (sqlserver_endpoint_imp.c:5528)
03190811: 2023-08-18T06:39:57 [TARGET_LOAD ]E: Begin bulk failed (sqlserver_endpoint_imp.c:4268)
Could you please check and advise is it due to the keepalive mentioned above. Our target endpoint is Microsoft Azure SQL Database and source is Oracle.
We can see on target end-point advanced settings there is an option called "Additional ODBC connection properties:" currently no values used here. Our replicate version is 2022.11.0.765.
Regards,
Adersh
Hello @adershb ,
We noticed you opened a dedicated article TCP Provider: Error code 0x68, let's trace the issue in the new article.
Best Regards,
John.