Digital Exchange

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

Hi Eoin,

In the calculation stage, does it need an equals sign between Timeout and 1000?  i.e. Timeout=1000;

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

Thanks Charlie, I have tried that but no luck

Hi Eoin,

In the calculation stage, does it need an equals sign between Timeout and 1000?  i.e. Timeout=1000;

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

Hi Eoin,

I am not well-versed in Snowflake, but I have a couple of ideas.

1. Does it work if you use query_timeout instead of timeout in the connection string? The Snowflake docs seem to suggest that may be the preferred keyword for an ODBC connection. https://docs.snowflake.com/en/developer-guide/odbc/odbc-parameters (Connection Parameters > Additional Connection Parameters)

2. It appears the timeout can be set in two places for Snowflake - for a single session or for the overall warehouse. It may be worth checking what the timeout is for the warehouse, because Snowflake apparently will use whichever value is lower.

I suspect the solution ultimately lies with Snowflake. I reviewed the ODBC object in Blue Prism again, and I am not seeing any indication that it truncates or overrides the timeout or any values within the connection string.

Cheers

Thanks Charlie, I have tried that but no luck

Hi Eoin,

In the calculation stage, does it need an equals sign between Timeout and 1000?  i.e. Timeout=1000;

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

Hi Charlie, 
So in relation to query 1 it has not made a difference.

In relation to query 2 Snowflake uses a 15 minute timeout in our company as the default.

I have discovered if I reduce the amount of columns I get results but if I query more then 5 I get a different error to the one here? (This has been following some changes we made)

The error now is 

ERROR [HY000] [Snowflake][Snowflake] (25)   Result download worker error: Worker error: [Snowflake][Snowflake] (4)   REST request for URL https://sfc-ie-ds1-7-customer-stage.s3.eu-west-1.amazonaws.com/I've removed rest of URL for security reasons failed: CURLerror (curl_easy_perform() failed) - code=35 msg='SSL connect error' osCode=9 osMsg='Bad file descriptor'.

Hi Eoin,

I am not well-versed in Snowflake, but I have a couple of ideas.

1. Does it work if you use query_timeout instead of timeout in the connection string? The Snowflake docs seem to suggest that may be the preferred keyword for an ODBC connection. https://docs.snowflake.com/en/developer-guide/odbc/odbc-parameters (Connection Parameters > Additional Connection Parameters)

2. It appears the timeout can be set in two places for Snowflake - for a single session or for the overall warehouse. It may be worth checking what the timeout is for the warehouse, because Snowflake apparently will use whichever value is lower.

I suspect the solution ultimately lies with Snowflake. I reviewed the ODBC object in Blue Prism again, and I am not seeing any indication that it truncates or overrides the timeout or any values within the connection string.

Cheers

Thanks Charlie, I have tried that but no luck

Hi Eoin,

In the calculation stage, does it need an equals sign between Timeout and 1000?  i.e. Timeout=1000;

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

Hi Eoin,

The ODBC object in Blue Prism is very lightweight in the sense that it only forwards a connection string or query to a database system, and it returns either result sets or error messages generated by the underlying database system.

A few things I could recommend checking:
1. Validate your connection string is correct for Snowflake - the screenshot in the first post looks more like a connection string for a SQLite system.
2. Validate the Snowflake ODBC driver you installed is a) 32-bit and b) configured correctly. Do you have a database team or DBA that could verify this or validate ODBC connectivity outside of Blue Prism?

Hi Charlie, 
So in relation to query 1 it has not made a difference.

In relation to query 2 Snowflake uses a 15 minute timeout in our company as the default.

I have discovered if I reduce the amount of columns I get results but if I query more then 5 I get a different error to the one here? (This has been following some changes we made)

The error now is 

ERROR [HY000] [Snowflake][Snowflake] (25)   Result download worker error: Worker error: [Snowflake][Snowflake] (4)   REST request for URL https://sfc-ie-ds1-7-customer-stage.s3.eu-west-1.amazonaws.com/I've removed rest of URL for security reasons failed: CURLerror (curl_easy_perform() failed) - code=35 msg='SSL connect error' osCode=9 osMsg='Bad file descriptor'.

Hi Eoin,

I am not well-versed in Snowflake, but I have a couple of ideas.

1. Does it work if you use query_timeout instead of timeout in the connection string? The Snowflake docs seem to suggest that may be the preferred keyword for an ODBC connection. https://docs.snowflake.com/en/developer-guide/odbc/odbc-parameters (Connection Parameters > Additional Connection Parameters)

2. It appears the timeout can be set in two places for Snowflake - for a single session or for the overall warehouse. It may be worth checking what the timeout is for the warehouse, because Snowflake apparently will use whichever value is lower.

I suspect the solution ultimately lies with Snowflake. I reviewed the ODBC object in Blue Prism again, and I am not seeing any indication that it truncates or overrides the timeout or any values within the connection string.

Cheers

Thanks Charlie, I have tried that but no luck

Hi Eoin,

In the calculation stage, does it need an equals sign between Timeout and 1000?  i.e. Timeout=1000;

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

I'm a bit confused here Charlie, as the smaller query works. If it was a connection issue wouldn't it be a case that none of them would work?

Hi Eoin,

The ODBC object in Blue Prism is very lightweight in the sense that it only forwards a connection string or query to a database system, and it returns either result sets or error messages generated by the underlying database system.

A few things I could recommend checking:
1. Validate your connection string is correct for Snowflake - the screenshot in the first post looks more like a connection string for a SQLite system.
2. Validate the Snowflake ODBC driver you installed is a) 32-bit and b) configured correctly. Do you have a database team or DBA that could verify this or validate ODBC connectivity outside of Blue Prism?

Hi Charlie, 
So in relation to query 1 it has not made a difference.

In relation to query 2 Snowflake uses a 15 minute timeout in our company as the default.

I have discovered if I reduce the amount of columns I get results but if I query more then 5 I get a different error to the one here? (This has been following some changes we made)

The error now is 

ERROR [HY000] [Snowflake][Snowflake] (25)   Result download worker error: Worker error: [Snowflake][Snowflake] (4)   REST request for URL https://sfc-ie-ds1-7-customer-stage.s3.eu-west-1.amazonaws.com/I've removed rest of URL for security reasons failed: CURLerror (curl_easy_perform() failed) - code=35 msg='SSL connect error' osCode=9 osMsg='Bad file descriptor'.

Hi Eoin,

I am not well-versed in Snowflake, but I have a couple of ideas.

1. Does it work if you use query_timeout instead of timeout in the connection string? The Snowflake docs seem to suggest that may be the preferred keyword for an ODBC connection. https://docs.snowflake.com/en/developer-guide/odbc/odbc-parameters (Connection Parameters > Additional Connection Parameters)

2. It appears the timeout can be set in two places for Snowflake - for a single session or for the overall warehouse. It may be worth checking what the timeout is for the warehouse, because Snowflake apparently will use whichever value is lower.

I suspect the solution ultimately lies with Snowflake. I reviewed the ODBC object in Blue Prism again, and I am not seeing any indication that it truncates or overrides the timeout or any values within the connection string.

Cheers

Thanks Charlie, I have tried that but no luck

Hi Eoin,

In the calculation stage, does it need an equals sign between Timeout and 1000?  i.e. Timeout=1000;

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

@Eoin McAndrew 

FYI - We've just uploaded a new version of the Data - ODBC VBO which includes a new optional input parameter on both the Execute and ExecuteNonQuery actions which allows you to set the timeout of the underlying .NET objects that are used to run the query. This may help with your timeout issue. The default timeout in .NET is 30 seconds, so you could try increasing it to say 60 seconds.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange

Original Message:
Sent: 04-17-2023 15:13
From: Eoin McAndrew
Subject: ODBC Timeout Error

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

------------------------------
Eoin McAndrew
N/A
Laya Healthcare
Europe/London
------------------------------

Thanks @Eric Wilson , I have deployed this but I keep getting an error with the execute of object reference not set to an instance of the object ?

------------------------------
Eoin McAndrew
N/A
Laya Healthcare
Europe/London

Original Message:
Sent: 05-16-2023 22:55
From: Eric Wilson
Subject: ODBC Timeout Error

@Eoin McAndrew 

FYI - We've just uploaded a new version of the Data - ODBC VBO which includes a new optional input parameter on both the Execute and ExecuteNonQuery actions which allows you to set the timeout of the underlying .NET objects that are used to run the query. This may help with your timeout issue. The default timeout in .NET is 30 seconds, so you could try increasing it to say 60 seconds.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange

Original Message:
Sent: 04-17-2023 15:13
From: Eoin McAndrew
Subject: ODBC Timeout Error

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

------------------------------
Eoin McAndrew
N/A
Laya Healthcare
Europe/London
------------------------------

Hi @Eoin McAndrew 

Apologies for that. We've addressed that issue. Please download the .bprelease file again.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange

Original Message:
Sent: 05-17-2023 12:24
From: Eoin McAndrew
Subject: ODBC Timeout Error

Thanks @Eric Wilson , I have deployed this but I keep getting an error with the execute of object reference not set to an instance of the object ?

------------------------------
Eoin McAndrew
N/A
Laya Healthcare
Europe/London

Original Message:
Sent: 05-16-2023 22:55
From: Eric Wilson
Subject: ODBC Timeout Error

@Eoin McAndrew 

FYI - We've just uploaded a new version of the Data - ODBC VBO which includes a new optional input parameter on both the Execute and ExecuteNonQuery actions which allows you to set the timeout of the underlying .NET objects that are used to run the query. This may help with your timeout issue. The default timeout in .NET is 30 seconds, so you could try increasing it to say 60 seconds.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange

Original Message:
Sent: 04-17-2023 15:13
From: Eoin McAndrew
Subject: ODBC Timeout Error

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

------------------------------
Eoin McAndrew
N/A
Laya Healthcare
Europe/London
------------------------------