Executing a long running stored procedure async using timer triggered Azure function

Question

Having a bit of a problem getting a stored procedure being executed from an Azure timer triggered function and am struggling to find out why.

I have a bunch of small stored procedures that do some row updates and inserts based on some logic which are executed using Azure timer triggered functions with no issues however one of them misbehaves.

The stored procedure is rather adaptive index defragmentation from here: (https://github.com/microsoft/tigertoolbox/tree/master/AdaptiveIndexDefrag)

It takes about a minute to run and it is scheduled to run during the night on a daily basis.

Here is the code to execute function responsible executing the above sproc:

    public static class MyFunction {
    [FunctionName("MyFunction")]
    public static async Task Run([TimerTrigger("0 0 0 */1 * * ")] TimerInfo myTimer, ILogger log) {
        await using var conn = new SqlConnection("connection string");

        await using var command = new SqlCommand("do_the_thing", conn) {CommandType = CommandType.StoredProcedure};
        try {
            command.Connection.Open();
            var result = await command.ExecuteScalarAsync();
            log.LogInformation($"Query result: {result}");
        }
        catch (Exception ex_) {
            log.LogError(ex_, "OH NO!");
        }

        log.LogInformation("Went smoothly");
    }
}

The result value is -1, Azure monitor says query executed successfully, but looking at the logs and load sql server side, the stored procedure have not been run.

All other functions running smaller sprocs reuse the above code. I have made a test function with a test sproc using the exact code and it works fine, however executing index defrag one always fails but no error or exception is given. All looks fine, the only indicator that tells me that sproc failed to run is the duration timer of the Azure function responsible executing index defrag sproc, it is always too short, max I have seen is 2.5 sec when it should be over a minute.

Any help will be appreciated.

Nothing in the logs? What kind of error handling is in the SQL, perhaps it is swallowing an error? Editting and adding the actual SQL would help — Charlieface
Nothing in the logs as far as I can see. Perhaps there are some other logs I should dig in to but I am no SQL expert. The code of the stored procedure is given in the link. Its over 2500 lines. — Serge
That procedure uses a table called tbl_AdaptiveIndexDefrag_Analysis_log is there anything in there? What parameters are you calling it with? In other words: can you edit and post the full calling code? — Charlieface
The sproc is called with default parameters. I puss nothing to it. After more testing I am beginning to be convinced that it is the sproc that does throw something that is not being caught. I run the same "EXECUTE do_the_thing" in SSMS and .NET code. Under SSMS it works fine but from code... no luck. — Serge
What about passing @debugMode=1 parameter, and receiving the messages via SqlConnection.InfoMessage event? — Charlieface

Joseph Xu Joseph Xu · Accepted Answer · 2021-05-24T01:39:31

finally you tried to pass @debugMode=1 parameter , then you got a message that the code has limited permissions just to do some tasks from SqlConnection.InfoMessage event. When the permissions are granted, you can successfully execute the stored procedure async via timer triggered Azure function.

Executing a long running stored procedure async using timer triggered Azure function

1 Answers