SqlCommandBuilder.DeriveParameters() incorrectly includes the database name in TypeName property values for SqlParameters for table-valued parameters

[Metritutus]

Description

When using SqlCommandBuilder.DeriveParameters() with a SqlCommand instance to execute a stored procedure with a table-valued parameter, the SqlParameter instance it produces (for the aforementioned table-valued parameter) has a TypeName property value which incorrectly includes the database name.

Attempting to execute this SqlCommand will produce an exception message explaining that it is not valid to include the database name in the type name for a table-valued parameter.

Exception details

Exception message: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 1 ("@ids"), row 0, column 0: Data type 0xF3 (user-defined table type) has a non-zero length database name specified.  Database name is not allowed with a table-valued parameter, only schema name and type name are valid.
Stack trace:
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   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.ExecuteScalar()
   at ConsoleApp1.Program.Main(String[] args) in C:\Users\<redacted>\Documents\Visual Studio 2019\Projects\ConsoleApp1\ConsoleApp1\Program.cs:line 30

To reproduce

Create the following user-defined table type:

CREATE TYPE [IdList] AS TABLE
(
    [Id] [int] NOT NULL
)

Create the following stored procedure:

CREATE PROCEDURE [spTestTableValuedParameter](@ids [IdList] READONLY)
AS
BEGIN
    SELECT [Id], ([Id] + 1) AS [IdPlusOne]
    FROM @ids
    ORDER BY [Id] DESC
END

Create a console application with the following code (filling in the connection string as appropriate):

using Microsoft.Data.SqlClient;
using System;
using System.Data;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "<redacted>";
            string storedProcedureName = "spTestTableValuedParameter";

            using var connection = new SqlConnection(connectionString);

            connection.Open();

            using var command = new SqlCommand(storedProcedureName, connection) { CommandType = CommandType.StoredProcedure };
            SqlCommandBuilder.DeriveParameters(command);

            var values = new DataTable();
            values.Columns.Add("Id", typeof(int));

            values.Rows.Add(1);
            values.Rows.Add(2);
            values.Rows.Add(3);

            command.Parameters["@ids"].Value = values;

            var result = command.ExecuteScalar();

            Console.WriteLine(result); // Should be 3.
        }
    }
}

Run the above console application.

Expected behavior

The stored procedure should execute without error.

Further technical details

Microsoft.Data.SqlClient version: 2.1.2
.NET target: .NET 5
SQL Server version: Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 20 2021 17:51:58
Operating system: Windows 10

Additional context
From Googling, it looks like this may be a long-standing bug dating back to System.Data.SqlClient that has never been fixed: https://stackoverflow.com/a/10035953

[Wraith2]

Odd, I thought I'd fixed this years ago in dotnet/corefx#35549

[Metritutus]

It does indeed appear to work if I use System.Data.SqlClient instead.

The problem therefore appears to exclusively be with Microsoft.Data.SqlClient.

Perhaps Microsoft.Data.SqlClient lacks the improvements made to System.Data.SqlClient in your referenced pull-request?

[Wraith2]

It looks like that PR didn't get pulled into this repo. I can port it once my outstanding changes to SqlParameter have been merged.

[cheenamalhotra]

@Wraith2

Yes it looks like got missed. Please also consider dotnet/corefx#41008 while porting over the fix.

[Wraith2]

The xml schema was one of the things i wanted to look at so yes i can work on that at the same time.

[mtreiser]

This is happening again. .NET Framework 4.6.1, SQL Compatibility Level 2008. What is the fix ?

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect.
Table-valued parameter 3 ("@AllergyID"), row 0, column 0: Data type 0xF3 (user-defined table type) has a non-zero length
database name specified. Database name is not allowed with a table-valued parameter, only schema name and type name are valid.

[Wraith2]

Fix is here in netfx:

SqlClient/src/Microsoft.Data.SqlClient/netfx/src/Microsoft/Data/SqlClient/SqlCommand.cs

Lines 3599 to 3603 in cb4f568

	// the constructed type name above is incorrectly formatted, it should be a 2 part name not 3
	// for compatibility we can't change this because the bug has existed for a long time and been
	// worked around by users, so identify that it is present and catch it later in the execution
	// process once users can no longer interact with with the parameter type name
	p.IsDerivedParameterTypeName = true;

and here in netcore:

SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs

Lines 3497 to 3501 in cb4f568

	// the constructed type name above is incorrectly formatted, it should be a 2 part name not 3
	// for compatibility we can't change this because the bug has existed for a long time and been
	// worked around by users, so identify that it is present and catch it later in the execution
	// process once users can no longer interact with with the parameter type name
	p.IsDerivedParameterTypeName = true;

So if you have found a new way to cause this we will need a reproduction sample and for you to open a new issue.

[mtreiser]

I believe my issue is moot since I'm not using Microsoft.Data.Sqlclient for it isn't compatible with .NET 4.6.1 Framework (I tried to install it). My solution is using System.Data.Sqlclient. I do find it odd that I'm experiencing the same issue however with this namespace.

[Wraith2]

https://www.nuget.org/packages/Microsoft.Data.SqlClient is compatible with netfx 4.6.2

[mtreiser]

next framework in the dropdown for me is 4.7.2 and I don't know if my employer will want to make this change at this stage in the development

[Wraith2]

You probably only need to install the developer pack to use it https://dotnet.microsoft.com/en-us/download/visual-studio-sdks , look for 4.6.2 on the page.

If you can't upgrade to Microsoft.Data.SqlClient just use the workaround which is to set the TypeName property of the parameter to null after the name has been assigned. You can see in the workaround code in the linked PR that it was written so that anyone using the workaround would not be broken by the change.

[mtreiser]

tried that and still got "Object reference not set to an instance of an object"
My test code... ATestProc just inserts to a table with one column called "ID" and is an INT field

` dt.Columns.Add("ID", typeof(int));

        for (int i = 1; i < 10; i++)
            dt.Rows.Add(i);

        try
        {
            //bool bResult = o_cSQL.RunSP("ATestProc", dt);

            bool bResult = false;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CSSdbConnStr"].ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("ATestProc", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter p = cmd.Parameters.Add(new SqlParameter("@Data", SqlDbType.Structured));
                    p.Value = dt;
                    p.TypeName = null;

                    bResult = (bool)cmd.ExecuteScalar();
                }
            }
            if (!bResult)
                lblMessage.Text = "INSERT failed";
        }
        catch(Exception ex)
        {
            Common.CommonUtilities.FatalError("Test", "btnTest_Click", o_cSQL.CmdSQL, ex.Message, ex.StackTrace);
        }`

if I run it like so
bool bResult = o_cSQL.RunSP("ATestProc", dt);
it fails with
ex.Message "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect.
Table-valued parameter 1 ("@DaTa"), row 0, column 0: Data type 0xF3 (user-defined table type) has a non-zero length
database name specified. Database name is not allowed with a table-valued parameter, only schema name and type name are valid."

The code for o_cSQL namespace and function RunSP is

`public bool RunSP(string StoredProcedureName, params object[] parameterValues)
{
SqlConnection cnn = GetConnection();
SqlCommand o_Command = new SqlCommand(StoredProcedureName, cnn);
o_Command.CommandType = CommandType.StoredProcedure;

        try
        {
            SqlCommandBuilder.DeriveParameters(o_Command);

            Int32 i = 0;
            foreach (SqlParameter Parameter in o_Command.Parameters)
            {
                if (Parameter.Direction == ParameterDirection.Input | Parameter.Direction == ParameterDirection.Output)
                {
                    Parameter.Value = parameterValues[i];
                    i = i + 1;
                }
            }

            m_CmdSQL = o_Command.CommandText + GetSQLParameters(o_Command.Parameters);

            using (o_Command)
            {
                o_Command.ExecuteNonQuery();
            }
            return true;
        }

        catch (Exception ex)
        {
            Common.CommonUtilities.FatalError(PageName, "RunSP", m_CmdSQL, ex.Message.ToString(), ex.ToString());
            return false;
        }

        finally
        {
            o_Command = null;
            cnn.Dispose();
        }
    }`

And I did move the check for the parameter SqlDbType into RunSP function after the if check on Parameter.Direction, and it still failed with the same exception.

[Wraith2]

All I can suggest is following the link https://stackoverflow.com/questions/9921121/unable-to-access-table-variable-in-stored-procedure/10035953#10035953 from the original issue in this thread and seeing if it helps.

System.Data.SqlClient is deprecated and cannot be fixed.
It is already fixed in Microsoft.Data.SqlClient.