DAB stored procedure timeout

[ax365]

Hi everyone,

What is the setting to define the timeout for a stored procedure accessed by DAB?

I'm getting the following timeout error:

{"error":{"code":"DatabaseOperationFailed","message":"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.","status":500}}

^^ The stored procedure needs a bit more time to complete.

[ax365]

The connection timeout in database connection string doesn't make a difference.

[aaronburtle]

DAB doesn't interact with your server's query timeout and won't be able to change it for you. The error message you see is being returned to you from your server through DAB. To change your query time out will require you to do so directly with the server that you are using with DAB. If it is an MSSQL Server I know Sql Server Management Studio has a Connections property where this can be changed.

[ax365]

Thanks @aaronburtle,

I will see if there is something I can set (remote timeout) on the database/server side.

For my purposes, we are using a Synapse Dedicated Pool:

• The stored procedure completes in 39s inside of SSMS
• The stored procedure errors-out / timeout when calling the DAB API

It seems like it was acknowledged that DAB "should" have this feature based on this? #1481

[aaronburtle]

It seems like it was acknowledged that DAB "should" have this feature based on this? #1481

Yes, it is in our backlog, but no current ETA.

[lanedsmu]

I know this is set as answered, but I wanted to add a solution that we use, that I couldn't find easily online: adding the Command Timeout= parameter to the connection string allows us to execute commands that run longer than the default (or other configured) timeouts:

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.4.27/dab.draft.schema.json",
  "data-source": {
  "database-type": "mssql",
  "connection-string": "Server=myServer;Database=myDb;Trusted_Connection=True;TrustServerCertificate=True;Encrypt=True;Command Timeout=240;",
...
}