当另一个未完成的结果集处于活动状态时，触发器返回结果集和/或在SET NOCOUNT OFF下运行 [英] A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active

问题描述

我有2台通过低速WAN连接的服务器，并且我们正在运行带有合并复制的SQL Server 2008.

I have 2 servers connected over a low speed wan and we're running SQL Server 2008 with Merge replication.

在订阅服务器上，有时在尝试插入新行时，出现此错误:

At the subscriber, sometimes when attempting to insert new rows, I get this error:

触发器返回结果集和/或在SET NOCOUNT OFF的情况下运行 而另一个出色的结果集处于活动状态.

A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active.

• 我的数据库没有任何触发器；唯一的触发器是由合并复制创建的触发器
• 此外，只要发生此错误，它就会自动回滚现有事务
• 我正在使用DataTables和TableAdapters来使用事务插入和更新数据库
• My database doesn't have any triggers; the only triggers are the one created by the Merge replication
• Also, whenever this error occurs it automatically rolls back the existing transaction
• I am using DataTables and TableAdapters to insert and update the database using transactions

我检查过的内容

1. 数据库日志文件大小小于50Mb
2. 检查了僵尸交易的源代码(因为一开始我无法检索到实际错误)
3. 检查了两个服务器之间的连接，发现连接已阻塞

问题:

1. 如何避免这种行为以及为什么会首先发生?
2. 为什么要取消未结交易?

推荐答案

触发器将返回各种结果集"，即受影响的行数. (受影响的1行)//或n行...." 我不知道为什么将其解释为结果集，但确实如此.

The trigger is returning a "resultset" of sorts, the number of rows affected. "(1 row(s) affected) // or n rows...." I don't know why this is being interpreted as a resultset but it is.

我今天有一个类似的问题，我意识到可以通过在触发器结尾处设置SET NOCOUNT OFF来解决此问题.仅在触发器顶部设置SET NOCOUNT ON是不够的.

I had a similar issue today and I realized that this issue can be fixed by putting a SET NOCOUNT OFF towards the end of the trigger. Just having the SET NOCOUNT ON at the top of the trigger is not sufficient.

我要引用的触发器是应用程序中的预制插入"语句.这很可能是引发SQL错误的语句.

The trigger in which I am referring is the pre-made "insert" statement in your application.This is most likely the statement throwing the SQL error.

现在您可以使用sp_configure'禁止触发器产生的结果'1 ，但是这将禁用整个数据库，并且在您期望其他触发器返回结果集的情况下可能不希望这样做.

Now you can use sp_configure 'disallow results from triggers' 1, but, that will disable this for the entire database and that may not be desirable, in case you are expecting some other trigger to return a resultset.

资料来源我曾经描述过，如果我的答案不够用，您将遇到同样的问题.另外，MSDN曾经说过

The OP of the Source I used described the exact same problem you are having if my answer doesn't suffice. Also, MSDN had said

在将来的SQL Server版本中将删除从触发器返回结果集的功能.避免在新的开发工作中从触发器返回结果集，并计划修改当前正在执行此操作的应用程序.为了防止触发器在SQL Server 2005中返回结果集，请将禁止来自触发器的结果"选项设置为1.在以后的SQL Server版本中，此选项的默认设置为1.

The ability to return result sets from triggers will be removed in a future version of SQL Server. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this. To prevent triggers from returning result sets in SQL Server 2005, set the disallow results from triggers Option to 1. The default setting of this option will be 1 in a future version of SQL Server.

这篇关于当另一个未完成的结果集处于活动状态时，触发器返回结果集和/或在SET NOCOUNT OFF下运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！