PostgreSQL链接服务器执行INSERT语句报错Msg 7343问题咨询

在对接PostgreSQL的SQL Server链接服务器上执行跨表数据拉取、插入操作时触发报错，已确认使用64位版本SSMS及ODBC配置，SELECT查询可正常运行，测试阶段已通过limit 10将查询结果限制为10行。
执行的SQL代码如下：

with datum as 
(
select * from openquery([DDL_64],'SELECT e.*
, ib.sales_order_number                                              IB_SALES_ORDER_NUMBER
, ib.sales_order_date                   IB_SALES_ORDER_DATE
, ib.sales_order_type                   IB_SALES_ORDER_TYPE
, ib.model_number                       IB_MODEL_NUMBER
, ib.product_item_number                IB_ITEM_NUMBER
, ib.product_item_description           IB_MODEL_DESC
, ib.install_base_status                IB_STATUS
, ib.cs_customer_name                   IB_Install_Customer
, ib.install_at_country_name            IB_Install_Country
, ib.model_so_line_num                  IB_Model_SO_Line_Number
, current_date                          last_Run_Date

FROM ws_sls_core.dd_enclosures_forarsdashboard e 
left join  ws_sls_core.w_customer_ib_profile ib 
                ON (e.encl_serial = ib.serial_number and ib.model_number not in (''NA'',''UN'')) limit 10')
)
insert into openquery([DDL_64],'select sn1,encl,encl_model,encl_serial,encl_slots,encl_state,encl_oem_name,encl_owm_value,parsed_ts,disk,disk_slot,disk_manf,disk_capacity,disk_sn,disk_type,disk_firmware,disk_model,id,asupid,sn,epoch,external,model,model_str,sn_valid,eth0mac,tz,lic_feature,lic_key,ib_sales_order_number,ib_sales_order_date,ib_sales_order_type,ib_model_number,ib_item_number,ib_model_desc,ib_status,ib_install_customer,ib_install_country,ib_model_so_line_number,last_run_date
from ws_sls_core.dd_enclosure_ib_f_temp ')
select * from datum

返回的报错信息：

OLE DB provider "MSDASQL" for linked server "DDL_64" returned message "the driver cound't identify inserted rows". Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "MSDASQL" for linked server "DDL_64" could not INSERT INTO table "[MSDASQL]".

按优先级从高到低依次排查：

• 检查目标表主键/唯一约束配置
  这是该报错最高发的诱因：MSDASQL驱动通过OPENQUERY执行增改操作时，必须能通过唯一标识定位每一行，目标表ws_sls_core.dd_enclosure_ib_f_temp如果没有主键、没有非空字段构成的唯一索引，驱动无法识别插入后的行位置，就会直接抛出该错误。先给目标表补充主键或唯一非空索引后重试。
• 修正OPENQUERY插入段的写法
  当前插入语句中，OPENQUERY内的查询没有加过滤条件，驱动会先拉取目标表现有全量数据做行匹配校验，哪怕插入的源数据只有10行，驱动依然会做全表行识别。给查询子句加where 1=0条件，让驱动只获取表结构不拉取现有数据，绕开不必要的校验：

  insert into openquery([DDL_64],'select sn1,encl,encl_model,encl_serial,encl_slots,encl_state,encl_oem_name,encl_owm_value,parsed_ts,disk,disk_slot,disk_manf,disk_capacity,disk_sn,disk_type,disk_firmware,disk_model,id,asupid,sn,epoch,external,model,model_str,sn_valid,eth0mac,tz,lic_feature,lic_key,ib_sales_order_number,ib_sales_order_date,ib_sales_order_type,ib_model_number,ib_item_number,ib_model_desc,ib_status,ib_install_customer,ib_install_country,ib_model_so_line_number,last_run_date
  from ws_sls_core.dd_enclosure_ib_f_temp where 1=0')
  select * from datum
  

• 检查ODBC驱动与数据源配置
  优先使用PostgreSQL官方发布的64位ODBC驱动，不要使用第三方兼容驱动。打开64位ODBC数据源管理器，找到DDL_64对应的数据源配置项：
  • 关闭“插入/更新时自动识别行”类的校验选项
  • 开启批量插入支持
  • 关闭行版本自动校验功能
• 调整链接服务器的提供程序选项
  在SSMS中找到DDL_64链接服务器对应的MSDASQL提供程序配置：
  • 勾选“允许进程内运行”，跨进程调用ODBC时插入操作的行识别逻辑大概率失效
  • 勾选“嵌套查询支持”“仅零级架构”
  • 取消勾选“对插入/更新/删除语句启用事务”选项，保存后重试
• 绕过OPENQUERY的兼容限制
  如果以上调整都无效，换用EXEC AT写法直接在PostgreSQL端执行插入，完全跳过MSDASQL的本地行识别校验：

  -- 先把查询结果存入SQL Server本地临时表
  select * into #temp_datum from openquery([DDL_64],'SELECT e.*
  , ib.sales_order_number                                              IB_SALES_ORDER_NUMBER
  , ib.sales_order_date                   IB_SALES_ORDER_DATE
  , ib.sales_order_type                   IB_SALES_ORDER_TYPE
  , ib.model_number                       IB_MODEL_NUMBER
  , ib.product_item_number                IB_ITEM_NUMBER
  , ib.product_item_description           IB_MODEL_DESC
  , ib.install_base_status                IB_STATUS
  , ib.cs_customer_name                   IB_Install_Customer
  , ib.install_at_country_name            IB_Install_Country
  , ib.model_so_line_num                  IB_Model_SO_Line_Number
  , current_date                          last_Run_Date
  FROM ws_sls_core.dd_enclosures_forarsdashboard e 
  left join  ws_sls_core.w_customer_ib_profile ib 
                  ON (e.encl_serial = ib.serial_number and ib.model_number not in (''NA'',''UN'')) limit 10')
  
  -- 拼接插入SQL，直接在PG侧执行
  DECLARE @insert_sql NVARCHAR(MAX)
  SELECT @insert_sql = STRING_AGG(
    N'(' + CONCAT_WS(',', 
      QUOTENAME(ISNULL(CAST(sn1 AS NVARCHAR(MAX)),'NULL'),''''),
      QUOTENAME(ISNULL(CAST(encl AS NVARCHAR(MAX)),'NULL'),''''),
      -- 其余字段按实际类型依次做转义拼接即可
      QUOTENAME(ISNULL(CAST(last_run_date AS NVARCHAR(MAX)),'NULL'),'''')
    ) + N')',
    N','
  ) FROM #temp_datum
  
  SET @insert_sql = N'INSERT INTO ws_sls_core.dd_enclosure_ib_f_temp (sn1,encl,encl_model,encl_serial,encl_slots,encl_state,encl_oem_name,encl_owm_value,parsed_ts,disk,disk_slot,disk_manf,disk_capacity,disk_sn,disk_type,disk_firmware,disk_model,id,asupid,sn,epoch,external,model,model_str,sn_valid,eth0mac,tz,lic_feature,lic_key,ib_sales_order_number,ib_sales_order_date,ib_sales_order_type,ib_model_number,ib_item_number,ib_model_desc,ib_status,ib_install_customer,ib_install_country,ib_model_so_line_number,last_run_date) VALUES ' + @insert_sql
  EXEC (@insert_sql) AT [DDL_64]
  

  该方案兼容性最高，基本不会触发驱动层的插入校验问题。

内容的提问来源于stack exchange，提问作者usama.nomani