Blue Prism Product

last person joined: 8 hours ago 

Chat about Blue Prism products
Expand all | Collapse all

SQL Timeout after 31 seconds

Jump to Best Answer
  • 1.  SQL Timeout after 31 seconds

    Posted 09-28-2020 08:54
      |   view attached
    ​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
    ------------------------------

    Attachment(s)



  • 2.  RE: SQL Timeout after 31 seconds

    Posted 09-28-2020 18:12
    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
    ------------------------------



  • 3.  RE: SQL Timeout after 31 seconds

    Posted 09-29-2020 02:24
    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
    ------------------------------



  • 4.  RE: SQL Timeout after 31 seconds
    Best Answer

    Posted 09-29-2020 10:47
    Edited by Nicholas Zejdlik 26 days ago
    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
    ------------------------------



  • 5.  RE: SQL Timeout after 31 seconds

    Posted 09-29-2020 12:13
    thanks its working

    greetings

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