从链接服务器插入存储过程的结果

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

Where tst is a stored procedure that returns IDs.

其中tst是返回ID的存储过程。

If I run the exec on its own it works fine

如果我自己运行exec它工作正常

EXEC [linkedserver].remoteDB.dbo.tst

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

但是当我把它作为插入的一部分时,我得到了这个错误

"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."

“OLE DB提供程序”SQLNCLI“用于链接服务器”linkedserver“返回消息”伙伴事务管理器已禁用其对远程/网络事务的支持。“。消息7391,级别16,状态2,行2无法执行操作,因为链接服务器“linkedserver”的OLE DB提供程序“SQLNCLI”无法启动分布式事务。“

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

一台机器运行SQL Server 2005,另一台机器运行“分布式事务协调器”服务。

2 个解决方案

#1

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

听起来像是对远程事务的支持没有正确启用。

Have you tried following the instructions here:

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

The partner transaction manager has disabled its support for remote/network transactions

合作伙伴事务管理器已禁用其对远程/网络事务的支持

#2

I think the reason is when we call EXEC alone it's not called within a transaction, so no problem. When we call INSERT EXEC, it's called within a txn, so the remote server has to enable network txn support. But we can avoid doing that with this:

我认为原因是当我们单独调用EXEC时,它不会在事务中被调用,所以没问题。当我们调用INSERT EXEC时,它会在txn中调用,因此远程服务器必须启用网络txn支持。但我们可以避免这样做:

https://dba.stackexchange.com/questions/46541/how-to-insert-in-table-from-remote-stored-procedure-without-creating-a-distribut

#1