链接服务器'(null)'的奇怪的OLE DB提供程序'STREAM'返回了列'[!BulkInsert] .Value'的无效数据错误 [英] Weird "OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].Value' error

查看:280
本文介绍了链接服务器'(null)'的奇怪的OLE DB提供程序'STREAM'返回了列'[!BulkInsert] .Value'的无效数据错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

使用的软件:Windows 7 64位Ultimate,.Net 4,SQL Server 2008 R2.

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

选择@@ version返回:

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) 

要进行复制,并假设您具有本地sql server 2008 R2实例,请将以下代码粘贴到linqpad中,然后将其作为程序运行.

To reproduce, and assuming you have a local sql server 2008 R2 instance, paste the following code in linqpad and run it as a Program.

它炸毁了:

链接服务器'(null)'的OLE DB访问接口'STREAM'返回了列'[!BulkInsert] .Value'的无效数据.

OLE DB provider 'STREAM' for linked server '(null)' returned 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();
  }
}

我可能会与Microsoft一起创建案例,但我很好奇是否以前有人看过它,以及是否有任何解决方法.似乎并非所有的零都是相等的.

I will probably create a case with Microsoft, but I was curious to see if someone else saw this before, and if there are any workarounds. It seems that not all the zeros are created equal.

只需更新:

我向Microsoft开了一个案子.他们花了将近2个月的时间才提出了一些模糊的,未记录在案的dbcc标志,该标志关闭了对通过批量复制注入变量列的值的验证.该案在不同团队之间被退回(由印度私人承包商提供支持),他们从未解决过根本问题,我认为这与以下几行产生的价值以及批量复制代码的处理方式有关:

I opened a case with Microsoft. It took them almost 2 months to come up with some obscure undocumented dbcc flag that turns off the validation of the values that get pumped via the bulk copy into the variant column. The case was bounced between the different teams (the support was provided by Indian private contractors) and they never got to the root problem which I think it is related to the value produced by the following lines and how it is handled by the bulk copy code :

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

因此,总而言之,这令人失望,我放弃了,因为这种情况很少发生.

So, to conclude, it was disappointing and I gave up because it is a rare occurrence.

推荐答案

由Microsoft指示并且我最终使用的解决方案是使用以下未记录的标志:DBCC TRACEON(7307,-1).您可以在连接级别或服务器级别启用它.

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

这篇关于链接服务器'(null)'的奇怪的OLE DB提供程序'STREAM'返回了列'[!BulkInsert] .Value'的无效数据错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
相关文章
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆