Logic App Standard - Sql Server Timeout

[mikeholdorf]

We have a simple Logic App that picks up a message from Service Bus and then calls to Sql Server. The query to SQL can take up to 2 minutes, but the timeout for this connector is 30 seconds. The documents show that we can use the setting: Runtime.ServiceProviders.Sql.QueryExecutionTimeout, but this has no impact at all.

How can we get a SQL Query to not timeout at 30 seconds?

Also, running under Node 12, this returns back a 502 Bad Gateway message. If you change to run under Node 14, it will return a 200 with an empty result set. This looks like it is a bug (or we shouldn't use Node 14).

AB#27002629

[AbodeSaafan]

Hello, for setting the time out can you please confirm to add the appsetting "ServiceProviders.Sql.QueryTimeout" to your workflow app settings (local.settings.json for VSCode and appsettings on portal) with the timespan value that you want. The default for the action is 2 minutes (30 seconds was a bug that was fixed)

As for the node question, we only support node 12 at this moment

[mikeholdorf]

We do not have anything in our local.settings.json specified.

I just ran a few tests and it looks like it does now timeout just over 2 minutes. So if this was a bug, it is now fixed. I still have 50 seconds in my hosts.json and I even added this as a configuration setting, but it doesn't seem to have any impact on how long the query runs and times out. Does this need to be a Configuration setting when running in Azure or is the host.json the only place this is needed?

Thanks

[AbodeSaafan]

It should be a Configuration setting when running in the portal and when running locally it should be in local.settings.json.

The setting to set is "ServiceProviders.Sql.QueryTimeout" and the value is a timespan so for example "00:05:00" would be 5 minutes. Updating this setting on portal should trigger the app to restart and then the setting would take affect. Let me know if that works for you

[mikeholdorf]

So this is now working. I added ServiceProviders.Sql.QueryTimeout as a configuration entry. I did use 00:01:00 and it times out in 1 minute. I then changed this to 00:01:30 and it timed out in 90 seconds.

The Documentation has this setting as Runtime.ServiceProviders.Sql.QueryTimeout. I guess we don't need the Runtime prefix.

[AbodeSaafan]

Sounds good, I have noted that we need a documentation update and filed that on our team to update it.

[SaleemEcolab]

i added ServiceProviders.Sql.QueryTimeout in both host file and configuration ,but its not working as it takes default timeout as 2 mins

[SaleemEcolab]

Here is the code {
"version": "2.0",
"extensionBundle": {
"id": "Microsoft.Azure.Functions.ExtensionBundle.Workflows",
"version": "[1.*, 2.0.0)"
},
"extensions":{
"workflow":{
"settings":{
"ServiceProviders.Sql.QueryTimeout":"00:05:00"

}
}
}
}

[SaleemEcolab]

@AbodeSaafan AbodeSaafan any idea on this ?

[mikeholdorf]

I just did a test using a new Logic App Standard instance and used ServiceProviders.Sql.QueryTimeout in the Configuration with a value of 00:03:00. I put a huge delay in a SQL stored procedure and it timed out at 16 minutes. Make sure your connection string doesn't have a connection timeout specified...the default is usually 30 seconds

[AzureDev0012]

@mikeholdorf
I configured the SQL Query Execute Action in Logic App Workflow and added the "ServiceProviders.Sql.QueryTimeout" :"00:03:00" in Configuration, but no luck on Timeout increase.
And I have used managed identity for SQL Connection.