链接服务器 SQLNCLI 问题。 “没有活动的交易”
我尝试执行存储过程并将其结果插入临时表中，并且收到以下消息：
无法执行该操作，因为 OLE DB 提供程序“SQLNCLI” 对于链接服务器“MyServerName”无法开始分布式 交易。用于链接服务器的 OLE DB 提供程序“SQLNCLI” “MyServerName”返回消息“没有活动的事务。”。
我的查询如下所示：
INSERT INTO #TABLE
EXEC MyServerName.MyDatabase.dbo.MyStoredProcedure Param1, Param2, Param3
确切的列号，名称，问题不是结果。
MSDTC 在两台计算机上都被允许并启动，远程过程调用也是如此。
这些机器不在同一域中，但我可以从我的机器执行远程查询并获取结果。我什至可以执行存储过程并查看其结果，只是无法将其插入另一个表中。
编辑
哦，我忘了提及，存储过程不会触发任何触发器。它仅将记录插入到它自己创建的用于数据处理的临时表中。
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.".
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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
好吧，在学习了大量教程并进行了大量研究之后，我更改了我认为它工作所需的所有配置，但仍然没有。
今天，由于不间断故障，我们不得不强制重新启动开发服务器的电源，当我们启动服务器时，你猜怎么着？有用！
因此，仅作为记录，我更改了一些特定的 MSDTC 配置，将其添加为链接服务器并允许 RPC IN 和 OUT，并将 RPC 配置更改为“无需身份验证”或类似内容。
我记得在某处读到，更改此配置后，需要重新启动，即使 Windows 说它已经重新启动了该服务。
自从我改变它以来，我已经重新启动了我的服务器两次，但它仍然不起作用。但就像今天一样，完全关闭并打开后，它就可以工作了！
至于语法，我保持不变。
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.
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.
您还必须检查 IP 网络配置中的 DNS 名称解析。
例如，您有一台名为 server-a.mydomain.com 的服务器和另一台名为 server-b.otherdomain.com 的服务器，登录 server-a 并执行“ping server-b”（不带域）。
如果它响应“Ping 请求无法找到主机服务器-b。请检查名称并重试。”这就是问题所在。
转到控制面板>网络连接>右键单击网卡>属性>互联网协议>属性>高级> DNS>按顺序附加此 DNS 后缀。
这里添加本地域：mydomain.com，然后添加远程域：otherdomain.com
单击“确定”直到退出
现在，如果您执行“ping server-b”，它应该响应如下内容：
现在再次尝试执行分布式事务。
You also have to check the DNS name resolution in the IP network configuration.
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).
If it responds "Ping request could not find host server-b. Please check the name and try again." that is the problem.
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
Now if you do the "ping server-b" it should repond something like:
Now try to again to execute the distributed transaction.
我有幸安全地重新启动链接服务器连接两端的 SQL Server 服务。我不必重新启动机器。
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.
您尝试过使用 openquery 吗？
Have you tried using openquery?