从链接的存储过程插入到本地表 [英] inserting to a local table from a linked stored procedure

问题描述

我有一个从.net代码调用的sproc，该代码会在本地表上进行更新，然后将其插入到其他本地表中，这些值是从对链接服务器的远程过程调用中获得的.

I have a sproc that is called from .net code which does an update on a local table and then is supposed to insert to a different local table values that are obtained from a remote procedure call to a linked server.

每次尝试从链接服务器插入结果时，都会收到此错误消息.如果我取出insert into语句，但是将远程过程调用保留在原处，则可以正常工作.这是错误:

I get this error message every time I try to insert the results from the linked server. If i take out the insert into statement, but leave the remote procedure call in place, it works fine. Here is the error:

System.Data.SqlClient.SqlException:无法执行该操作 执行此操作是因为链接服务器的OLE DB提供程序"SQLNCLI10" "MyLinkedServer"无法开始分布式事务. OLE DB 链接服务器"MyLinkedServer"的提供程序"SQLNCLI10"已返回 消息事务管理器已禁用其对 远程/网络事务."

System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "MyLinkedServer" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI10" for linked server "MyLinkedServer" returned message "The transaction manager has disabled its support for remote/network transactions.".

这是我正在使用的简单代码:

this is the simple code I'm using:

INSERT INTO MyLocalTable
EXEC [MyLinkedServer].[MyRemoteDatabase].[dbo].[usp_MySproc] @MyParam

这里有什么问题的任何想法，或者关于如何将远程过程的结果插入本地表的任何建议?

any ideas what's wrong here or any suggestions of how I can insert the results from the remote procedure to a local table?

推荐答案

其中一个sql专家能够通过将启用对分布式事务的提升"更改为false来解决此问题.

one of the sql guys was able to resolve this by changing the "enable promotion of distributed transactions" to false.

这篇关于从链接的存储过程插入到本地表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！