从链接的存储过程插入到本地表 [英] 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屋!