The type initializer for 'System.Data.SqlClient.TdsParser' threw an exception error while calling Stored Procedure from Azure function

Question

I have an Azure Event hub with readings from my smart electricity meter. I am trying to use an Azure Function to write the meter readings to an Azure SQL DB. I have created a target table in the Azure SQL DB and a Stored Procedure to parse a JSON and store the contents in the table. I have successfully tested the stored procedure.

When I call it from my Azure Function however I am getting an error: The type initializer for 'System.Data.SqlClient.TdsParser' threw an exception. For testing purposes, I have tried to execute a simple SQL select statement from my Azure Function, but that gives the same error. I am lost at the moment as I have tried many options without any luck. Here is the Azure function code:

#r "Microsoft.Azure.EventHubs"

using System;
using System.Text;
using System.Data;
using Microsoft.Azure.EventHubs;
using System.Data.SqlClient;
using System.Configuration;
using Dapper;

public static async Task Run(string events, ILogger log)
{
    var exceptions = new List<Exception>();

    try
      {
            if(String.IsNullOrWhiteSpace(events))
                return;
            try{
                string ConnString = Environment.GetEnvironmentVariable("SQLAZURECONNSTR_azure-db-connection-meterreadevents", EnvironmentVariableTarget.Process);

                using(SqlConnection conn = new SqlConnection(ConnString))
                {
                    conn.Execute("dbo.ImportEvents", new { Events = events }, commandType: CommandType.StoredProcedure);
                }

            } catch (Exception ex) {
                log.LogInformation($"C# Event Hub trigger function exception: {ex.Message}");
            }
        }
        catch (Exception e)
        {
            // We need to keep processing the rest of the batch - capture this exception and continue.
            // Also, consider capturing details of the message that failed to process so it can be processed again later.
            exceptions.Add(e);
        }

    // Once processing of the batch is complete if any messages in the batch failed process throw an exception so that there is a record of the failure.

    if (exceptions.Count > 1)
        throw new AggregateException(exceptions);

    if (exceptions.Count == 1)
        throw exceptions.Single();
}

The events coming in are in JSON form as follows

{ 
   "current_consumption":450,
   "back_low":0.004,
   "current_back":0,
   "total_high":13466.338,
   "gas":8063.749,
   "current_rate":"001",
   "total_low":12074.859,
   "back_high":0.011,
   "timestamp":"2020-02-29 22:21:14.087210"
}

The stored procedure is as follows:

CREATE PROCEDURE [dbo].[ImportEvents]
@Events NVARCHAR(MAX)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- Insert statements for procedure here
    INSERT INTO dbo.MeterReadEvents

    SELECT * FROM OPENJSON(@Events) WITH (timestamp datetime2, current_consumption int, current_rate nchar(3), current_back int, total_low numeric(8, 3), back_high numeric(8, 3), total_high numeric(8, 3), gas numeric(7, 3), back_low numeric(8, 3))
END

I have added a connection string of type SQL AZURE and changed {your password} by the actual password in the string. Any thoughts on how to fix this issue or maybe how to get more logging as the error is very general?.

Answer 1

I managed to fix this exception by re-installing Microsoft.Data.SqlClient.SNI. Then clean and rebuild your project.

Answer 2

I managed to fix the issue by changing the Runtime version to ~2 in the Function App Settings.

Does this mean this is some bug in runtime version ~3 or should there be another way of fixing it in runtime version ~3?

Answer 3

I might be late to the party, in my case the cause of the error was "Target runtime" when publishing, I developed on windows machine but was transferring the file to linux, the solution was to change target runtime to the correct one, initial it was win-x64(merely because I started off by deploying locally), see screenshot below