链接服务器的SQLNCLI问题. “没有交易是活跃的". [英] Linked servers SQLNCLI problem. "No transaction is active"

问题描述

我正在尝试执行存储过程，只是将其结果插入临时表中，而我收到以下消息:

Im trying to execute a stored procedure and simply insert its results in a temporary table, and I'm getting the following message:

由于OLE DB提供程序"SQLNCLI"而无法执行该操作 链接服务器"MyServerName"的操作无法开始分布式 交易.链接服务器的OLE DB提供程序"SQLNCLI" "MyServerName"返回消息没有事务处于活动状态.".

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "MyServerName" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "MyServerName" returned message "No transaction is active.".

我的查询如下:

INSERT INTO #TABLE
EXEC MyServerName.MyDatabase.dbo.MyStoredProcedure Param1, Param2, Param3

确切的列号，名称，不是问题所在.

Exact column number, names, the problem is not the result.

允许在两台计算机上启动MSDTC，也可以在远程过程中调用.

MSDTC is allowed and started in both computers, Remote procedure calling too.

这些机器不在同一个域中，但是我可以从我的机器上执行远程查询并获得结果.我什至可以执行存储过程并查看其结果，只是不能将其插入另一个表中.

The machines are not in the same domain, but I can execute remote queries from my machine and get the result. I can even execute the stored procedure and see its results, I just can't insert it in another table.

编辑

哦，我忘了提及，存储过程不会触发任何触发器.它只会在临时表中插入记录，并创建临时表以进行数据处理.

Oh I forgot to mention, the stored procedure doesn't fire any trigger. It only inserts records in temporary tables which it creates itself for data treating.

推荐答案

在经过大量教程并对其进行了大量研究之后，我已经更改了所有我认为对其起作用所必需的配置，但仍然没有

Well, after following lots of tutorials and researching a lot about it, I had changed all the configuration I thought was necessary for it to work, but it still didn't.

今天，由于不间断的故障，我们不得不在开发服务器上强制重新启动电源，而当我们启动服务器时，您猜怎么着?可行！

Today we had to force a power reboot on our development server because of a faulty no-break, and when we booted up the server, guess what? It works!

仅作记录，我已经更改了一些特定的MSDTC配置，将其添加为链接服务器并允许RPC IN和OUT，并且将RPC配置更改为不需要验证".

So just for the record, I've changed some specific MSDTC configuration, added it as a linked server and allowed RPC IN and OUT, and changed the RPC configuration for 'NO AUTHENTICATION REQUIRED' or something like that.

我记得在某处读过，更改此配置后，即使Windows表示它已经重新启动了该服务，也需要重新启动.

I remember reading somewhere that after you changed this configuration, a reboot was required, even though Windows says that it has already restarted the service.

自从更改服务器以来，我已经像两次一样重新引导了服务器，但它仍然无法正常工作.但是直到今天，在完全关闭然后再打开之后，它仍然可以工作！

I had rebooted my server like... twice since I changed it, and it still didn't work. But as today, after a complete turn off and turn on, it works!

至于语法，我保持不变.

As for the syntax, I kept the same.

这篇关于链接服务器的SQLNCLI问题. “没有交易是活跃的".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！