How can I avoid a SqlClient timeout error during execution of a C# Azure Function?

Question

I have created a function app time trigger with C#. Inside the logic, I call and execute a stored procedure that I have created in SQL Server.

The execution of the stored proc takes more than 8 minutes, and I have got the following error in Azure function apps log :

2018-04-02T11:03:46.409 [Error] Exception while executing function: Functions.AbarIomWeeklyUsage. mscorlib: Exception has been thrown by the target of an invocation. .Net SqlClient Data Provider: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The wait operation timed out. 2018-04-02T11:03:46.425 [Error] Function completed (Failure, Id=1b85e7ef-ea52-4fd3-ab79-f27d188c5cac, Duration=30323ms)

So far I tried 2 things : 1. leverage the connection timeout in application connection string 2. add timeout in host.json

But it still gives me the same error 30 seconds timeout.

Anyone experienced the same or have solution for this?

Clypeate

Do you get sql timeout after 30 seconds or function timeout after 8 minutes? – Goniometer

The exception message is looking like this is an issue of your stored procedure timeout out, not the functions runtime. What did you set your functions runtime timeout to be? – Under

i think this message is about sql timeout after 30s, but cannot find where to change the the conn timeout. my function timeout 10 minutes { "functionTimeout": "00:10:00" } – Clypeate

i added commandtimeout when sqlcommand, let' see how this work. NB : this line code fix it for me, thx for the help and attention – Clypeate

Answer 1

You are certainly exceeding the default command timeout (the default is 30 seconds). If you don't set SqlCommand.CommandTimeout you will get a SqlException. Set the command's CommandTimeout in code (there is no configuration for this) to 10 minutes (600 seconds), same as your function timeout.

Datura

Answer 2

Here's a code snippet on how to do this:

using (SqlCommand cmd = new SqlCommand(text, conn))
{
        cmd.CommandTimeout = 60 * 60; // seconds
        cmd.Parameters.AddWithValue("@parameter1", myparameter1);

        // Execute the command and log the # rows affected.
        var rows = await cmd.ExecuteNonQueryAsync();

        log.Info($"{rows} rows were deleted");
}

Representational