Weird "OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].Value' error

c#-4.0 sqlbulkcopy sql-server-2008-r2

Question

SQL Server 2008 R2,.Net 4, and Windows 7 64-bit Ultimate were utilized.

choose @@version yields:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 

The following code should be pasted into Linqpad and executed as a Program to replicate the issue, assuming you have a local instance of SQL Server 2008 R2.

It explodes with:

For the associated server "(null)," the OLE DB provider "STREAM" reported incorrect data for the field "[!BulkInsert]. Value'.

void Main()
{
  SqlConnection cn = new SqlConnection("data source=localhost;Integrated Security=SSPI;initial catalog=tempdb;Connect Timeout=180;");
  cn.Open();

  IList<decimal> list = new List<decimal>() {-8m, 8m};

  decimal result = list.Sum(x => x);

  Console.WriteLine(result == 0);

  string tableName = "#test";

  CreateTemporaryTable(cn, tableName,
        String.Format(@"
          create table {0} (
            Value sql_variant
         );
       ", tableName));

  SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(cn);
  sqlBulkCopy.DestinationTableName = tableName;       

  DataTable dt = new DataTable();
  dt.Columns.Add("Value", typeof(object));
  dt.Rows.Add(result);
  sqlBulkCopy.WriteToServer(dt);

  sqlBulkCopy.Close();

  cn.Close();
}

// Define other methods and classes here


public static void CreateTemporaryTable(SqlConnection cn, string destinationTableName, string createTableStatement)
{
  string objectIdValue = (destinationTableName.StartsWith("#") ? "tempdb.." : "") + destinationTableName;
  string sql = String.Format(@" 
    if (object_id (N'{0}', N'U') is not null)
      begin
        drop table {1};
      end;
      {2}    
      ", objectIdValue, destinationTableName, createTableStatement);

//      Console.WriteLine(sql);
  SqlCommand cmd = new SqlCommand(sql, cn);
  try
  {
    cmd.ExecuteNonQuery();
  }
  finally
  {
    cmd.Dispose();
  }
}

I'll certainly file a complaint with Microsoft, but I was wondering if anybody else has seen this and if there were any remedies. It seems that there are several types of zeros.

A brief update

I made a complaint to Microsoft. It took them nearly two months to discover a mysterious, undocumented dbcc setting that disables validation of the data put into the variation column through bulk copy. The assistance was given by Indian private contractors, and the case was passed back and forth between the various teams without ever addressing the underlying issue, which, in my opinion, is connected to the value generated by the following lines and how the bulk copy code handles it:

  IList<decimal> list = new List<decimal>() {-8m, 8m};
  decimal result = list.Sum(x => x);

As a result, I gave up since it is an uncommon event and it was disheartening.

1
14
8/26/2014 11:12:30 PM

Accepted Answer

The workaround suggested by Microsoft, which I ultimately used, is to utilize the DBCC TRACEON undocumented flag (7307, -1). It may be turned on either at the server level or at the connection level.

4
10/15/2015 7:40:34 PM

Popular Answer

If the data type for the column "value" is float, your issue is probably that you sent sql server a double.NaN, which it dislikes.

Please be aware of a duplicate call. dv will be set to double and TryParse("NaN", out dv) will joyfully return true. NaN

Thanks for reading, K.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow