从Linked Server视图复制表时reference列报错，求保留该列的解决方案

尝试通过以下SQL脚本从链接服务器视图复制数据到本地表：

SELECT 
    [order_no]
    , [cust_code]
    , [order_date]
    , [order_status]
    , [reference]
INTO dbo.Sales
FROM [BIT].[bit].[BI].[sales_order]

执行后触发错误：

Cannot get the data of the row from the OLE DB provider for linked server "BIT". Could not convert the data value due to reasons other than sign mismatch or overflow.

移除[reference]列后脚本可正常运行，且已确认该列数据类型与目标表匹配，单独复制该列时也会触发相同错误。需要在保留该列的前提下完成表复制。

• 显式强制转换数据类型
  即使表面数据类型匹配，链接服务器可能在底层数据传输时存在隐性转换问题，显式指定目标类型可以规避：

  SELECT 
      [order_no]
      , [cust_code]
      , [order_date]
      , [order_status]
      , CAST([reference] AS VARCHAR(255)) AS [reference] -- 根据实际存储内容调整类型和长度
  INTO dbo.Sales
  FROM [BIT].[bit].[BI].[sales_order]
  

  注意：要根据reference列实际存储的内容调整转换后的类型（比如NVARCHAR、TEXT等），确保覆盖所有数据长度。

• 检查并清洗特殊字符/无效数据
  链接服务器返回的reference列可能包含不可见字符、格式异常的NULL值或编码不兼容的数据。先排查异常行：

  -- 用OPENQUERY在远程服务器端执行检查，减少数据传输量
  SELECT [reference]
  FROM OPENQUERY(BIT, 'SELECT [reference] FROM bit.BI.sales_order WHERE [reference] IS NOT NULL')
  WHERE [reference] LIKE '%[^a-zA-Z0-9@._-]%' -- 过滤含特殊字符的行
  

  找到异常数据后，在查询中直接清洗：

  SELECT 
      [order_no]
      , [cust_code]
      , [order_date]
      , [order_status]
      , REPLACE(REPLACE([reference], CHAR(0), ''), CHAR(10), '') AS [reference] -- 移除空字符和换行符
  INTO dbo.Sales
  FROM [BIT].[bit].[BI].[sales_order]
  

• 调整链接服务器OLE DB设置
  打开链接服务器属性，找到对应OLE DB提供程序（比如SQL Server Native Client），启用「允许进程内」，禁用「针对分布式查询使用分布式事务」；也可以用SQL语句修改：

  EXEC sp_serveroption 'BIT', 'rpc out', 'true';
  EXEC sp_serveroption 'BIT', 'remote proc transaction promotion', 'false';
  

• 用OPENQUERY在远程端执行查询
  直接让远程服务器处理查询逻辑，再将结果导入本地表，避免本地服务器的隐性转换问题：

  SELECT *
  INTO dbo.Sales
  FROM OPENQUERY(BIT, 'SELECT [order_no], [cust_code], [order_date], [order_status], [reference] FROM bit.BI.sales_order')
  

• 分批导入数据
  如果数据量较大，单批读取容易触发转换错误，可分段插入：

  -- 先创建匹配结构的目标表
  CREATE TABLE dbo.Sales (
      [order_no] INT, -- 按实际数据类型定义各列
      [cust_code] VARCHAR(50),
      [order_date] DATE,
      [order_status] VARCHAR(20),
      [reference] VARCHAR(255)
  )
  
  -- 按order_no分段分批插入
  DECLARE @Start INT = 1, @End INT = 1000
  WHILE EXISTS(SELECT 1 FROM [BIT].[bit].[BI].[sales_order] WHERE order_no BETWEEN @Start AND @End)
  BEGIN
      INSERT INTO dbo.Sales
      SELECT [order_no], [cust_code], [order_date], [order_status], [reference]
      FROM [BIT].[bit].[BI].[sales_order]
      WHERE order_no BETWEEN @Start AND @End
      SET @Start = @End + 1
      SET @End = @End + 1000
  END
  

内容的提问来源于stack exchange，提问作者cvbn402517