topic RE: BP SQL VBO Timeout in Product Forum

BP SQL VBO Timeout

CraigTaylor — Mon, 03 Apr 2023 14:11:00 GMT

Hi all,

I am attempting to run a delete SQL query on a DB in Blue Prism. I have my flow built, but I am getting:

'Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Operation cancelled by user. The statement has been terminated.'

I have found the following support article and modified the Open code stage in the SQL VBO object with the supplied code, but still get timeouts after 30 seconds:

Support Center | Blue Prism Portal

Blueprism

remove preview

Support Center | Blue Prism Portal

View this on Blueprism >

The database being connected to is an Azure hosted SQL DB, could this be part of the issue?

------------------------------
Craig Taylor
------------------------------

RE: BP SQL VBO Timeout

John__Carter — Tue, 04 Apr 2023 08:36:00 GMT

Can't see the link Craig but I'm guessing it relates to specifying the timeout value. You can set this as part of the connection string, for example:
https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=dotnet-plat-ext-7.0

------------------------------
John Carter
Blue Prism
------------------------------

RE: BP SQL VBO Timeout

CraigTaylor — Tue, 04 Apr 2023 08:51:00 GMT

Hi John,

Looks like the forum consumed the link!

Tried your suggestion but still getting the 30 second timeout unfortunately.

------------------------------
Craig Taylor
------------------------------

RE: BP SQL VBO Timeout

John__Carter — Tue, 04 Apr 2023 09:14:00 GMT

Hmm strange. Are you sure the flow is passing through one of the Set Connection calcs and both will set the timeout value?

------------------------------
John Carter
Blue Prism
------------------------------

RE: BP SQL VBO Timeout

CraigTaylor — Tue, 04 Apr 2023 09:36:00 GMT

Yes, stepping through the process I can see it pass through the node with the Connection Timeout extra string above.

I am beginning to think it's perhaps something to do with the DB being Azure hosted and there is a setting overruling it there. I'll speak to our Azure admin to do some digging that side!

------------------------------
Craig Taylor
------------------------------

RE: BP SQL VBO Timeout

MichealCharron — Tue, 04 Apr 2023 12:24:00 GMT

@Craig Taylor

I can't get anything from the link either but I think what you are experiencing is a command timeout. The timeout setting in the provider string handles timeouts when trying to connect to a SQL server. The command timeout is a little bit more involved to handle. You have to modify the code to include the following line in the "Using cmd As New SqlCommand()" block:

cmd.CommandTimeout = Convert.ToInt32(timeout)

In this case, we created a "Timeout" parameter for the action that we can set to any value but you could hardcode a value (shameful) if you want.

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------