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

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:

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

  • 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

What I have checked:

  1. the database log file size is below 50Mb
  2. Checked the source code for Zombie transactions (since I wasn't able to retrieve the actual error at the beginning)
  3. Checked the connection between the two servers and found it congested

Questions:

  1. How to avoid this behavior and why it's occurring at first place?
  2. Why it's cancelling the open transaction?

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.

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.

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.

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.

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

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.

c#, SET NOCOUNT OFF while another outstanding result set was active trigger returned a resultset and/or was running with SET NOCOUNT� "A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active.". "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back."


I got the same issue and have a solution.

The problem was a stored-procedures with a result set, which was executed in this trigger.

It seems, that this stored-procedure leads the trigger to have a result set as well.

Finding code smells using SQL Prompt: the SET NOCOUNT problem, a trigger returned resultset and/or running set nocount off while outstanding result set activ. i want know meaning of that, example , how avoid� A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active It strikes me that in SQL profiler the MODIFY statement translates to:


I had the same problem.

The problem was an insert with select:

insert into table (...)
select ...

The problem was that select had a group statements and returned:

Warning: Null value is eliminated by an aggregate or other SET operation.

this causing the problem, I changed instructions max(field) by max(coalesce(field,'')) to avoid the null operation in max group function.

SQL Trigger error on Modify: SET NOCOUNT ON — mibuso.com, A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active. – Learn more on the� In all of the searching I did, it seemed like the problem was a new trigger on a table that suddenly caused the error; there were only two solutions suggested in all the sites I looked at: either fix the trigger (set nocount on, remove any select statements returning results), or set that advanced setting to 1, which affects all triggers on the


set in top of script

SET NOCOUNT ON;

SET NOCOUNT ON statement usage and performance benefits in , SET NOCOUNT ON doesn't affect the result that is returned. By default, SET NOCOUNT is set to OFF , at the SQL Server instance level, meaning that If a SET statement is run in a stored procedure or trigger, the previous value of the SET option is restored Inherited a database from another team? "A trigger returned a resultset and the server option 'disallow_results_from_triggers' is true." The disallow results from triggers option is applied at the Microsoft SQL Server instance level, and it will determine behavior for all existing triggers within the instance.


A trigger returned a resultset and/or was running with SET , Tags › A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active. November� A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active. My database doesn't have any triggers; the only triggers are the one created by the Merge replication


SQL Server Native Client error “A trigger returned a resultset”, A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active. It strikes me that in� A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active. 524: 16: No: A trigger returned a resultset and the server option 'disallow results from triggers' is true. 525: 16: No: The column that was returned from the nodes() method cannot be converted to the data type %ls.


This article explores the SET NOCOUNT ON statement, its usage and how it Output of a stored procedure in the results and message tab SET NOCOUNT OFF – By default, SQL Server shows the number of Let's create a SQL INSERT, Update trigger to capture the records for the insert, update values. "A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active." What does this mean exactly and why am I getting it? I'm probably not going to be able to change table definitions or mess with server configuration.