I am having huge problems with the current SQL Server 2019 (15.0.2070.41) and native compiled stored procedures when calling them from the Microsoft.Data.SqlClient
(1.1.0 and lower).
It is working when either using SQL Server 2017 or System.Data.SqlClient
.
I am also having the same problem in Java with the JDBC driver.
Apparently the TdsParser
is receiving data it doesn't expect from the SQL Server connection and is throwing an exception during the parsing.
System.InvalidOperationException: Internal connection fatal error.
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader()
Example code which should reproduce the problem:
using System;
using System.Data;
using Microsoft.Data.SqlClient;
namespace SqlDbTest
{
class Program
{
static void Main()
{
const string connString = "Server=localhost;Database=TestDb;Trusted_Connection=True";
const string prod = @"CREATE OR ALTER PROCEDURE dbo.NativeProdForTesting WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
SELECT NULL AS testcol
RETURN 0
END";
using (var con = new SqlConnection(connString))
{
using var createSp = con.CreateCommand();
createSp.CommandText = prod;
con.Open();
createSp.ExecuteNonQuery();
}
try
{
using var con = new SqlConnection(connString);
using var cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[NativeProdForTesting]";
cmd.Connection.Open();
using var reader = cmd.ExecuteReader();
Console.WriteLine("NO ERROR!");
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex);
}
}
}
}