Symptoms
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
[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
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.