Azure Function timer trigger failed runs execution of stored procedure

Question

I want to execute stored procedure on my azure function timer trigger. The function deployed successfully. But, when the function runs, I get this error stack trace messages

Microsoft.Azure.WebJobs.Host.FunctionInvocationException:
...
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw:
...
Inner exception System.ComponentModel.Win32Exception handled at System.Data.SqlClient.SqlConnection.OnError:
...

Here is the code,

[FunctionName("DimensionFactTableUpdate")]
public static async Task Run([TimerTrigger("%CronJobSchedule%")]TimerInfo myTimer, TraceWriter log)
{
    log.Info($"C# Timer trigger UpdateFactDimensionTable function executed at: {DateTime.Now}");
    var _connectionString = Environment.GetEnvironmentVariable("DbConnectionString");
    var _storedProcedureDimension = Environment.GetEnvironmentVariable("StoredProcedureDimension");

    if (string.IsNullOrEmpty(_connectionString) || string.IsNullOrEmpty(_storedProcedureDimension))            
        return;            

    #region UPDATE DIMENSION TABLE
    try
    {
        log.Info($"[START] Update dimension table at: {DateTime.Now}");
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand(_storedProcedureDimension, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                var status = await command.ExecuteNonQueryAsync();
            }
        }
        log.Info($"[END] Update Dimension table at: {DateTime.Now}");
    }
    catch(Exception ex)
    {
        log.Error(ex.ToString());
    }            
    #endregion 
}

Thanks.

======

EDITED :

Here is the exception messages,

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Could you include the full exception ? (ToString of the exception.) Instead of parts of the stacktrace — Preben Huybrechts, Jul 17 '20 at 06:50
And maybe validate `connectionstring` and `storedprecuderdimension` variables if they are not null or empty. — Preben Huybrechts, Jul 17 '20 at 06:56
@PrebenHuybrechts Here is the exception message > System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Thanks — Septovan, Jul 17 '20 at 07:39

Preben Huybrechts · Accepted Answer · 2020-07-17T08:07:05.367

So you received a Timeout exception:

This can mean 2 things. You are not able to login to the SQL Server. Does this exception occur on the Open() statement?
Then Investigate the login issues:

Is your connection string correct?
Is your database (tcp and port) accessible from azure? (this is Ok if you are using Azure SQL Databases)
Does the login have the correct permissions?
Check the SQL server error log sp_readerrorlog for more information why the login failed.

Otherwise if you receive the exception on ExecuteNonQueryAsync it's a timeout.

Option 1: The easy way, but not necessarily the best

Increase the timeout on the SqlCommand like

command.CommandTimeout = 60; // 60 seconds = 1 Minute

Option 2: The costly way

Scale out your SQL Server DB (assuming you are using Azure SQL)

Option 3 : The most time consuming way

Investigate why your Stored Procedure is slow, and solve this. This could have many issues, blocking, waits, poor or no indexes, cursors/whiles, ...

Option 2 worked well. But for sure, I need to improve my stored procedure. — Septovan, Jul 17 '20 at 08:16

Azure Function timer trigger failed runs execution of stored procedure

1 Answers1