从链接服务器插入存储过程的结果 [英] Inserting Results Of Stored Procedure From Linked Server

问题描述

是否可以将远程存储过程的结果插入到临时表中?例如

Is it possible to insert the results of a remote stored procedure into a temp table? For example

CREATE TABLE #test(id INT)
INSERT INTO #test 
EXEC [linkedserver].remoteDB.dbo.tst
DROP TABLE #test

tst是返回ID的存储过程.

Where tst is a stored procedure that returns IDs.

如果我自己运行exec则效果很好

If I run the exec on its own it works fine

EXEC [linkedserver].remoteDB.dbo.tst

但是当我将其作为插入内容的一部分时，会出现此错误

However when I put it as part of an insert I get this error

"链接服务器"linkedserver"的OLE DB提供程序"SQLNCLI"返回消息合作伙伴事务管理器已禁用其对远程/网络事务的支持.". Msg 7391，第16级，州2，第2行 由于链接服务器"linkedserver"的OLE DB提供程序"SQLNCLI"无法开始分布式事务，因此无法执行该操作."

"OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message "The partner transaction manager has disabled its support for remote/network transactions.". Msg 7391, Level 16, State 2, Line 2 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "linkedserver" was unable to begin a distributed transaction."

一台计算机正在运行SQL Server 2005，另一台计算机正在运行分布式事务处理协调器"服务.

One machine is running SQL Server 2005 and the other 2008, both are running the "Distributed Transaction Coordinator" service.

推荐答案

在我看来，未正确启用对远程事务的支持.

It sounds to me like the support for remote transactions has not been properly enabled.

您是否尝试过按照此处的说明进行操作:

Have you tried following the instructions here:

• 合作伙伴交易经理已禁用对远程/网络事务的支持

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