SQL 链接服务器 SQLNCLI 问题。“没有交易是活跃的”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2931957/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linked servers SQLNCLI problem. "No transaction is active"
提问by Smur
Im trying to execute a stored procedure and simply insert its results in a temporary table, and I'm getting the following message:
我试图执行一个存储过程并简单地将其结果插入到一个临时表中，我收到以下消息：
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.".
由于链接服务器“MyServerName”的 OLE DB 提供程序“SQLNCLI”无法开始分布式事务，因此无法执行该操作。链接服务器“MyServerName”的 OLE DB 访问接口“SQLNCLI”返回消息“没有活动的事务。”。
My query looks like this:
我的查询如下所示：
INSERT INTO #TABLE
EXEC MyServerName.MyDatabase.dbo.MyStoredProcedure Param1, Param2, Param3
Exact column number, names, the problem is not the result.
确切的列号，名称，问题不是结果。
MSDTC is allowed and started in both computers, Remote procedure calling too.
MSDTC 允许并在两台计算机上启动，远程过程调用也是如此。
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.
这些机器不在同一个域中，但我可以从我的机器执行远程查询并获得结果。我什至可以执行存储过程并查看其结果，只是无法将其插入到另一个表中。
EDIT
编辑
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.
哦，我忘了提，存储过程不会触发任何触发器。它只在它自己创建的临时表中插入记录以进行数据处理。
回答by Smur
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!
今天我们不得不在我们的开发服务器上强制重启电源，因为一个错误的不间断电源，当我们启动服务器时，你猜怎么着？有用！
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.
所以只是为了记录，我更改了一些特定的 MSDTC 配置，将其添加为链接服务器并允许 RPC 输入和输出，并将 RPC 配置更改为“无需身份验证”或类似内容。
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.
我记得在某处读到过，在您更改此配置后，需要重新启动，即使 Windows 表示它已经重新启动了该服务。
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.
至于语法，我保持不变。
回答by Javier Nogueira
You also have to check the DNS name resolution in the IP network configuration.
您还必须检查 IP 网络配置中的 DNS 名称解析。
For example, you have a server called server-a.mydomain.com and another one called server-b.otherdomain.com, log in the server-a and do a "ping server-b" (without the domain).
例如，您有一个名为 server-a.mydomain.com 的服务器和另一个名为 server-b.otherdomain.com 的服务器，登录 server-a 并执行“ping server-b”（不带域）。
If it responds "Ping request could not find host server-b. Please check the name and try again." that is the problem.
如果它响应“Ping 请求找不到主机服务器-b。请检查名称并重试。” 那就是问题所在。
Go to the Control Pannel > Network Connections > Right click in the network card > properties > Internet Protocol > Properties > Advanced > DNS > Append this DNS suffix in order. And here add the local domain: mydomain.com and then add the remote domain: otherdomain.com Click OK until you exit
进入控制面板 > 网络连接 > 在网卡中右键单击 > 属性 > Internet 协议 > 属性 > 高级 > DNS > 按顺序附加此 DNS 后缀。在这里添加本地域：mydomain.com 然后添加远程域：otherdomain.com 单击确定直到退出
Now if you do the "ping server-b" it should repond something like:
现在，如果您执行“ping server-b”，它应该响应如下内容：
Pinging server-b.otherdomain.com [192.168.1.2] with 32 bytes of data: Reply from 192.168.1.2: bytes=32 time=12ms TTL=64 Reply from 192.168.1.2: bytes=32 time=9ms TTL=64
ping server-b.otherdomain.com [192.168.1.2] 32字节数据： 来自192.168.1.2的回复：bytes=32 time=12ms TTL=64 来自192.168.1.2的回复：bytes=32 time=9ms TTL=64
Now try to again to execute the distributed transaction.
现在再次尝试执行分布式事务。
回答by Cryptc
I had the luxury of safely restarting the SQL Server services on both sides of the Linked Server connection. I did not have to reboot the machines.
我有幸在链接服务器连接的两端安全地重新启动 SQL Server 服务。我不必重新启动机器。
回答by thomas
Have you tried using openquery?
您是否尝试过使用 openquery？
insert into table select * from openquery(myservername, 'exec mydatabase.dbo.mystoredproc param1, param2, param3')
