如何在事务中使用链接服务器插入远程表? [英] How to Insert into remote table using Linked server withint Transaction?

问题描述

我的链接服务器设置正确，我可以执行以下查询.

My Linked server is setup correctly , I am able to perform below query.

插入[RemoteServer].[表] SELECT * FROM [LocalServer].[表]

INSERT INTO [RemoteServer].[Table] SELECT * FROM [LocalServer].[Table]

但是，当我在交易中执行相同的操作

BEGIN TRAN 插入[RemoteServer].[表] SELECT * FROM [LocalServer].[表] 提交TRAN

BEGIN TRAN INSERT INTO [RemoteServer].[Table] SELECT * FROM [LocalServer].[Table] COMMIT TRAN

我收到类似错误

用于链接的OLE DB提供程序"SQLNCLI" 服务器"66.70.123.202"已返回 消息没有事务处于活动状态.". Msg 7391，第16级，州2，第3行 无法执行该操作 因为OLE DB提供程序"SQLNCLI"用于 链接服务器"66.70.123.202"为 无法开始分布式 交易.

OLE DB provider "SQLNCLI" for linked server "66.70.123.202" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 3 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "66.70.123.202" was unable to begin a distributed transaction.

推荐答案

检查MS DTC服务是否同时在两台服务器上运行，并且已正确配置为允许分布式查询.

Check if MS DTC service is running on both servers and it is configured correctly to allow distribured queries.

这篇关于如何在事务中使用链接服务器插入远程表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！