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

Question

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?

Verlie

Answer 1

set in top of script

SET NOCOUNT ON;

Scoot

Answer 2

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.

Carnay

Answer 3

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.

Monotonous

Answer 4

I had a this issue but with an ASP Classic application using MSOLEDBSQL19 provider and a MARS connection, in a loop iterating over a record set and opening another one for inserting some data in a table having a trigger inserting some more data elsewhere. The issue was not happening systematically.

The trigger was having a set nocount on at its start, but also a useless set nocount off at its end.

Three solutions worked for me;

• Setting the disallow results from triggers Server Configuration Option, as proposed by umbreon222 in "his" answer (which seems to be mainly a bunch of copy-paste from the forum he cited at the end of his answer).
• Or removing the set nocount off at the end of the trigger, contrary to another suggestion in the previously mentioned answer.
• Or disabling MARS.

I did the two first. Activating disallow results from triggers is anyway recommended by its documentation. Of course it should be done only if the database does not have triggers which actually need to return some result sets.

Leaving the set nocount off with the previous option activated generates warnings stating the set is ignored due to the disallow option, so better remove it.

Disabling MARS in my case means having the inner opened record set open and close an unpooled connection at each iteration, which I want to avoid.

You can activate the disallow results from triggers by running in SSMS, as explained here by ckcampbell:

sp_configure N'show advanced options', 1;
go
reconfigure;
go
sp_configure N'disallow results from triggers', 1;
go
reconfigure;
go

(For an AWS RDS, which I am also using, parameters groups allow to set this option.)

Fatherhood