mysql - 使用分布式查询 (OpenQuery) 执行插入触发器时出错

Question

我的 MSSQL 触发器:

ALTER TRIGGER [dbo].[ioTrigger_dbo_vsolv_mst_tproddtchng]
ON [dbo].[vsolv_mst_tproddtchng]
for INSERT
AS 
BEGIN
SET NOCOUNT ON;

INSERT  OPENQUERY (LINKED,'SELECT proddtchng_product_gid,
    proddtchng_mrp,
    proddtchng_tndpwtax,
    proddtchng_tndpwotax,
    proddtchng_kldpwtax,
    proddtchng_kldpwotax,
    proddtchng_costprice,
    proddtchng_website,
    proddtchng_purpose,
    proddtchng_weight,
    proddtchng_suitable,
    proddtchng_workingrange,
    proddtchng_cutoff,
    proddtchng_timedelay,
    proddtchng_description,
    proddtchng_validfrom,
    proddtchng_validto,
    proddtchng_createby,
    proddtchng_createdate,
    proddtchng_isactive,
    proddtchng_isremoved
from  vsolv_line_tn.vsolv_mst_tproddtchng')
SELECT 
    proddtchng_product_gid,
    proddtchng_mrp,
    proddtchng_tndpwtax,
    proddtchng_tndpwotax,
    proddtchng_kldpwtax,
    proddtchng_kldpwotax,
    proddtchng_costprice,
    proddtchng_website,
    proddtchng_purpose,
    proddtchng_weight,
    proddtchng_suitable,
    proddtchng_workingrange,
    proddtchng_cutoff,
    proddtchng_timedelay,
    proddtchng_description,
    proddtchng_validfrom,
    proddtchng_validto,
    proddtchng_createby,
    proddtchng_createdate,
    proddtchng_isactive,
    proddtchng_isremoved
FROM
    inserted;
END

当我执行上述触发器时出现以下错误:

OLE DB provider "MSDASQL" for linked server "LINKED" returned message "[MySQL][ODBC 5.3(a) Driver]Optional feature not supported". Msg 7391, Level 16, State 2, Procedure ioTrigger_dbo_vsolv_mst_tproddtchng, Line 8 The operation could not be performed because OLE DB provider "MSDASQL" for linked server "LINKED" was unable to begin a distributed transaction.

我已经从Sqlserver2008创建了一个链接到Mysql的服务器，我已经配置了DTC并且它的状态是运行，自动，请帮助我如何克服上述错误。

Answer 1

它是您的 Distrubution transaction 属性，它在为链接服务器创建过程/触发器时中断，尤其是在非 MSSQL 连接的情况下

1) 右键点击链接服务器

2) 选择属性并启用此设置属性为假

运行过程:

EXEC master.dbo.sp_serveroption @server=N'SVRLINK',
 @optname=N'remote proc transaction promotion', @optvalue=N'false