FIX: A system assert occurs when a Transact-SQL stored procedure with a TVP argument is called from a SQLCLR procedure

Symptoms

Assume that you have a Transact-SQL stored procedure such as the following that takes a table-valued parameter (TVP) that has a constraint as an argument:

createtypeTestTvpType astable

(

id intprimarykey, --UNIQUE will also do the trick

dataint

);

go

createprocedureTestTvpProc @tvp TestTvpType readonly

as

begin

select*from @tvp;

end;

go

In a scenario where this procedure is called from a SQLCLR stored procedure and the constraint is violated for the argument, you may incorrectly receive a system assert, when you would expect to receive a "constraint violation" error message.

The following is an example of a SQLCLR procedure producing a constraint violation for the stored procedure:

[SqlProcedure]

publicstaticvoidClrProcCallingTsqlProcWithTvpArgument(outint sum)

{

using (SqlConnectionconnection = new SqlConnection("contextconnection=true"))

{

sum = 0;

DataTablemyDataTable = new DataTable("TestTvpType");

myDataTable.Columns.Add("id", typeof(Int32));

myDataTable.Columns.Add("data", typeof(Int32));

//Populate the TVP so it will trigger a PRIMARY KEY VIOLATION error

myDataTable.Rows.Add(1,1000);

myDataTable.Rows.Add(1,2000);

SqlParameterparameter = new SqlParameter();

parameter.ParameterName= "@tvp";

parameter.SqlDbType= System.Data.SqlDbType.Structured;

parameter.Value= myDataTable;

SqlCommandcommand = new SqlCommand("TestTvpProc",connection);

command.CommandType= CommandType.StoredProcedure;

command.Parameters.Add(parameter);

connection.Open();

using(SqlDataReader reader = command.ExecuteReader())

{

while(reader.Read())

{

…;

}

}

}

}

Resolution

This issue is fixed in the following cumulative updates for SQL Server: