Isolated process Azure function error while inserting record to SQL DWH pool

Question

I'm getting below error while trying to upsert a record in Synapse SQL Pool database using isolated process Azure Function.

System.Private.CoreLib: Exception while executing function: Functions.GetWeather. Microsoft.Azure.WebJobs.Host: Error while handling parameter _binder after function returned:. Microsoft.Azure.WebJobs.Extensions.Sql: Encountered exception during upsert and rollback. (Parse error at line: 2, column: 54: Incorrect syntax near 'WITH'.) (111214;An attempt to complete a transaction has failed. No corresponding transaction found.). Core Microsoft SqlClient Data Provider: Parse error at line: 2, column: 54: Incorrect syntax near 'WITH'.

SQL table definition:

CREATE TABLE WeatherDataV2 
(
    WeatherDataId BIGINT PRIMARY KEY NONCLUSTERED NOT ENFORCED, 
    createdDate DATETIME
);

Azure function code for isolated process

[Function("GetWeather")]
[SqlOutput("dbo.WeatherDataV2", connectionStringSetting: "SqlConnectionString")]
public WeatherData Run([TimerTrigger("%_runEveryHourCron%")] MyInfo myTimer, FunctionContext context)
{
   var weatherDataItem = new WeatherData()
   {
      WeatherDataId = DateTime.Now.Ticks,
      createdDate = DateTime.Now
   };
   return weatherDataItem;
}

public class WeatherData
{
    [Key]
    public long WeatherDataId { get; set; }
    public DateTime createdDate { get; set; }

}

Answer 1

With the following Code Snippet, it worked successfully and able to insert the data to Synapse SQL Pool Database Table:

1. Created Dedicated SQL Pool Database in the Synapse Studio
2. Created Timer Trigger Function App runs for every 2 minutes with initializing the WeatherDataItem as object with the properties followed WeatherDataId, createdDate and also as columns in the WeatherDataV2 Table in SQL.

Function1.cs:

using System.ComponentModel.DataAnnotations;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Extensions.Sql;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;


namespace Net6IsoFunApp02
{
    public class Function1
    {
        private readonly ILogger _logger;
        public string SqlConnectionString = "Server=tcp:demosynapse.sql.azuresynapse.net,1433;Initial Catalog=harisynapsesqlpoold;Persist Security Info=False;User ID=sqladminuser;Password=<mydbpassword>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
        public Function1(ILoggerFactory loggerFactory)
        {
            _logger = loggerFactory.CreateLogger<Function1>();
        }

        [Function("Function1")]
        [SqlOutput("dbo.WeatherDataV2", connectionStringSetting: "SqlConnectionString")]
        public WeatherData Run([TimerTrigger("0 */2 * * * *", RunOnStartup = true)] MyInfo myTimer)
        {
            _logger.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");
            _logger.LogInformation($"Next timer schedule at: {myTimer.ScheduleStatus.Next}");

            var weatherDataItem = new WeatherData()
            {
                WeatherDataId = DateTime.Now.Ticks,
                createdDate = DateTime.Now
            };
            _logger.LogInformation($"WeatherDataId is {weatherDataItem.WeatherDataId} and createdDate is {weatherDataItem.createdDate}");

            using (SqlConnection connection = new SqlConnection(SqlConnectionString))
            {
                String query = "INSERT INTO dbo.WeatherDataV2 (WeatherDataId, createdDate) VALUES (@WeatherDataId, @createdDate)";

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@WeatherDataId", weatherDataItem.WeatherDataId);
                    command.Parameters.AddWithValue("@createdDate", weatherDataItem.createdDate);
                  

                    connection.Open();
                    int result = command.ExecuteNonQuery();

                    // Check Error
                    if (result < 0)
                        Console.WriteLine("Error inserting data into Database!");
                }
            }
            return weatherDataItem;
        }
    }

    public class WeatherData
    {
        [Key]
        public long WeatherDataId { get; set; }
        public DateTime createdDate { get; set; }
    }
    public class MyInfo
    {
        public MyScheduleStatus ScheduleStatus { get; set; }
        public bool IsPastDue { get; set; }
    }

    public class MyScheduleStatus
    {
        public DateTime Last { get; set; }
        public DateTime Next { get; set; }
        public DateTime LastUpdated { get; set; }
    }
}

.csproj file:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <AzureFunctionsVersion>v4</AzureFunctionsVersion>
    <OutputType>Exe</OutputType>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Azure.Functions.Worker" Version="1.10.0" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.Sql" Version="1.0.122-preview" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.Timer" Version="4.0.1" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker.Sdk" Version="1.7.0" />
    <PackageReference Include="Microsoft.Azure.WebJobs.Core" Version="3.0.36" />
  </ItemGroup>
  <ItemGroup>
    <None Update="host.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Update="local.settings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
      <CopyToPublishDirectory>Never</CopyToPublishDirectory>
    </None>
  </ItemGroup>
  <ItemGroup>
    <Using Include="System.Threading.ExecutionContext" Alias="ExecutionContext" />
  </ItemGroup>
</Project>

References: SQL Connection Code taken from SO Q provided by @Andrew Paes