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

Question

Software Used: Windows 7 Ultimate 64-bit, .Net 4, SQL Server 2008 R2.

select @@ version returns:

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) 

To reproduce and suppose you have a local SQL Server 2008 R2, paste the following code into linqpad and run it as a program.

It explodes:

OLE DB provider "STREAM" for linked server "(null)" returns invalid data for column [! 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'm probably starting a business with Microsoft, but I was curious to know if anyone else had seen this before, and if there are any workarounds. It seems that not all zeros are created equal.

Just an update:

I opened a business with Microsoft. It took them almost 2 months to come up with some obscure undocumented dbcc flag that disables checking the values ​​that are pumped through a voluminous copy in the variant column. The matter was dropped between different teams (support was provided by Indian private contractors), and they never got into the root problem, which, in my opinion, is related to the value obtained in the following lines and how it is processed by the bulk copy code

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

So, in conclusion, it was disappointing, and I gave up, because it is a rare occurrence.

Answer 1

The solution pointed out by Microsoft and which I used is to use this undocumented flag: DBCC TRACEON (7307, -1). You can enable it at the connection level or at the server level.

Answer 2

If your data type is for the "float" column value, your probable problem is that you are sending the double.NaN server to sql, which it doesn't like.

Please note that a call of type double.TryParse ("NaN", out dv) will happily return true and set dv to double.NaN

Hope this helps, K.

Answer 3

This error occurs if double.NaN is in the table, so convert to null instead.

I used this extension method to get rid of this problem.

To use, add .MyToNullIfNan() to any double or double? and he will create a double? which is null if NaN .

 public static class MyToNullIfNanExtension { public static double? MyToNullIfNan(this double? result) { if (result.HasValue) { if (double.IsNaN(result.Value) || double.IsInfinity(result.Value)) { return null; } } return result; } public static double? MyToNullIfNan(this double result) { if (double.IsNaN(result) || double.IsInfinity(result)) { return null; } return result; } }