Timeout issue in using SQL in function app

Question

I'm using Powershell in a Function App hosted on Azure. Inside the app I'm executing queries on a database that take longer than 30 sec. (I'm not talking about the 5min limitation for function apps). Locally on my computer everything works fine but when I deploy to Azure and test-run I always get:

"ERROR: Exception calling "Fill" with "1" argument(s): "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." "

No matter what I set as Connection Timeout in the connection string the command "WAITFOR DELAY '00:00:45'" fails while setting 20 sec delay it succeeds. It seems to to me that somehow my settings are overridden in Azure.

Does anybody have an idea about what happens here ?

The code for the sql part is:

function Query_SQLServer{
    param($database,$query)
    $Conn  =  New-Object System.Data.SqlClient.SqlConnection
    $Conn.ConnectionString  = $env:SQLStageConnString
    $Conn.Open()
    $Command  =  New-Object  System.Data.SqlClient.SqlCommand
    $Command.Connection  =  $Conn
    $Command.CommandText  =  $query
    $Command.CommandTimeout = 300
    $Adapter = New-Object System.Data.SqlClient.SqlDataAdapter $Command
    $data = New-Object System.Data.DataSet
    $Adapter.Fill($data)
    return $data.Tables
}

Answer 1

Use the SqlCommand.CommandTimeout property in your code to increase the command timeout for the specific query you're executing.

$Command.CommandTimeout = 600 # Increase the timeout to 600 seconds or any other desired value