topic RE: SQL Timeout after 31 seconds in Product Forum

SQL Timeout after 31 seconds

KristianHolm1 — Mon, 28 Sep 2020 12:54:00 GMT

Hi Blueprism team I have tried to set the timeout in blueprism to 400 seconds. It was done inside the object Data - sql server connection to sql database. see attached file

Question why does it not work.?

greetings Kristian

------------------------------
Kristian Holm
RPA programmer
PenSam (Pension Company)
Copenhagen Denmark
------------------------------

RE: SQL Timeout after 31 seconds

wleung — Mon, 28 Sep 2020 22:12:00 GMT

Hello Kristian,

Perhaps I missed it but I did not see in your post or PDF what you meant by not working. Do you mean that the 400 second override is not having an effect and the query is still timing out after 30 seconds right?

The MS SQLClient SQLCommand docs ( https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=dotnet-plat-ext-3.1 ) had a couple of possible causes of this.

I also found quite a few articles about connection timeout where the SMEs were recommending setting the timeout values in the connection string. Could you perhaps share the non-private parts of your connection string here as well for analysis?

Lastly, this is not directly towards the issue but I would recommend paging these requests (as did several of the threads I read on timeouts.) Unless we are 100% sure of the volume of data, whatever static value we use today may not account for future behavior or an environment that is not performing optimally. Paging/chunking these queries and processing them incrementally will likely provide extra resiliency and help manage these timeout issues. To that end, we've made similar recommendation to customers that needed to add something like 400K items in a single "Add to Queue" action that was timing out.

------------------------------
Wing Ling Leung
Senior Product Consultant
Blue Prism Professional Services
------------------------------

RE: SQL Timeout after 31 seconds

KristianHolm1 — Tue, 29 Sep 2020 06:24:00 GMT

Hello blueprism

The issue is that it timeout after 30 seconds and not 400 seconds.
I have the connection string:
database="PSP_Prod_PSLiv";data source="PENFLXIPINS002\I02";Trusted_Connection=Yes

errormessage from blueprism:
Execution timeout has expired. The timeout period expired before the operation was performed or the server did not respond.

The code is from : https://portal.blueprism.com/customer-support/support-center#/path/Operational-Support/Performance/Database-Management/1360595772/Why-do-I-get-Execution-Timeout-Expired-The-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding-when-connecting-with-OLEDB.htm

But blueprism timeout after 30 seconds.?

In sql management studio it can execute the query in 50 seconds.

greetings

Kristian
Holm

------------------------------
Kristian Holm
RPA programmer
PenSam (Pension Company)
Copenhagen Denmark
------------------------------

RE: SQL Timeout after 31 seconds

NicholasZejdlik — Tue, 29 Sep 2020 14:47:00 GMT

I think the Blue Prism support article is a bit misleading; that code will only change the timeout of the "waitfor delay" query, it won't affect any queries afterwards because the timeout is specific to the SqlCommand object.

Assuming you're using the Get Collection action, you'll want to add a line to the code in the Get Data Table stage: cmd.CommandTimeout = 120 (however many seconds you need)

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

RE: SQL Timeout after 31 seconds

KristianHolm1 — Tue, 29 Sep 2020 16:13:00 GMT

thanks its working

greetings

------------------------------
Kristian Holm
RPA programmer
PenSam (Pension Company)
Copenhagen Denmark
------------------------------