Snowflake unable to validate connection

What Happened?

Issue:

Connection validation to Snowflake fails in Loome Integrate, because the number of result sets returned by the Snowflake server does not match the number the client application was expecting.

Impact:

The connection to Snowflake cannot be successfully established or validated within Loome Integrate, preventing the creation of a working connection resource and halting all data integration or querying processes using that resource.

Error:

‘Unable to validate Connection

Error: Actual statement count 2 did not match the desired statement count 1.’

Root Cause:

The session established by the service account (the Snowflake user configured in Loome Integrate) is implicitly or explicitly set to MULTI_STATEMENT_COUNT = 1. This parameter dictates that the session is restricted to accepting exactly one statement per batch.

How can you fix it?

Resolution

The solution is to run a preparatory SQL command in Snowflake before the failing query or validation attempt as per the following:

– Set the session to accept any number of statements (0 = unlimited)

ALTER SESSION SET MULTI_STATEMENT_COUNT = 0;

Return to Loome Integrate and validate the Snowflake connection. The connection should now succeed.