SQL server 2008 - appelant une procédure stockée depuis un CLR une procédure stockée avec un paramètre de tableau d'une valeur - Stack Overflow

The situation:

One Clr stored procedure that builds a data table and then it tries to call an SQL Stored procedure with a TVP parameter.

The code:

  public class tvp_test : System.Data.DataTable
  {
    public tvp_test()
    {
      this.Column.Add("field1",typeof(System.Int32));
      this.Column.Add("field2",typeof(System.Int32));
    }
  }

.............................................................

  {
    var tvp = new tvp_test();

.............................................................

   using(SqlConnection con = new SqlConnection("context connection=true"))
   {
      con.Open();
      var command = con.CreateCommand();

      command.CommandType = CommandType.StoredProcedure;
      command.CommandTest = "prc_test";
      command.Parameters.Add("@a",System.Data.SqlDbType.Structured);
      command.Parameters["@a"].Value = tvp;
      command.ExecuteNonQuery();

      con.Close();
   }
  }

......................................................................

create procedure prc_test
  @a tvp_test readonly
begin
  insert into #tmp (field1,field2) /* #tmp is created before the call of the CLR SP */
  select field1,field2 from @a
end

The problem:

The behavior is erratic. One time it works, after x (where x is random) calls it will give me y (where y is random) "severe error occurred" with no other details, and after that the cycle will start again. From what I see the problem is on the CLR SP side as when errors start to occur, the SQL SP can be changed and be faulty as the error remains the same. Maybe there is no love between CLR SP and TVPs, but I didn't find any reference to that.

As a side note, when it works it may work for a long time, but if I delete the SPs plan then it will start faulting. (can't find the logic in that "trigger" also)

Any thoughts ? Thank you in advance.

The situation:

One Clr stored procedure that builds a data table and then it tries to call an SQL Stored procedure with a TVP parameter.

The code:

  public class tvp_test : System.Data.DataTable
  {
    public tvp_test()
    {
      this.Column.Add("field1",typeof(System.Int32));
      this.Column.Add("field2",typeof(System.Int32));
    }
  }

.............................................................

  {
    var tvp = new tvp_test();

.............................................................

   using(SqlConnection con = new SqlConnection("context connection=true"))
   {
      con.Open();
      var command = con.CreateCommand();

      command.CommandType = CommandType.StoredProcedure;
      command.CommandTest = "prc_test";
      command.Parameters.Add("@a",System.Data.SqlDbType.Structured);
      command.Parameters["@a"].Value = tvp;
      command.ExecuteNonQuery();

      con.Close();
   }
  }

......................................................................

create procedure prc_test
  @a tvp_test readonly
begin
  insert into #tmp (field1,field2) /* #tmp is created before the call of the CLR SP */
  select field1,field2 from @a
end

The problem:

The behavior is erratic. One time it works, after x (where x is random) calls it will give me y (where y is random) "severe error occurred" with no other details, and after that the cycle will start again. From what I see the problem is on the CLR SP side as when errors start to occur, the SQL SP can be changed and be faulty as the error remains the same. Maybe there is no love between CLR SP and TVPs, but I didn't find any reference to that.

As a side note, when it works it may work for a long time, but if I delete the SPs plan then it will start faulting. (can't find the logic in that "trigger" also)

Any thoughts ? Thank you in advance.

Source