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

Applies to: SQL Server 2014 DeveloperSQL Server 2014 EnterpriseSQL Server 2014 Enterprise Core

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:

create typeTestTvpType as table   

(

  id int primary key, --UNIQUE will also do the trick

  data int

); 

go 

create procedureTestTvpProc @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]

public static voidClrProcCallingTsqlProcWithTvpArgument(out int 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:
 
 
 
 
Note This update causes the return of the correct "constraint violation" error message.
 

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

 

References


Learn about the terminology Microsoft uses to describe software updates.