Sql-server – Cannot set NOCOUNT to OFF inside the trigger execution because the server option “disallow_results_from_triggers” is true

sql-server-2008-r2

I have morning job schedule that starts at 5:30 AM….starting on 5-27-2014. I've begin implementing table replication on one of our heavily used tables…and use the replicated table for reporting purposes only.

  • Basic Publication
  • Type = Snapshot
  • Only one table article being used
  • No filters
  • Publish every 20 mins.
  • Replication agents checkup job every 10 mins

**The unique problem is that the 5:30 AM job calls a SP that has logic to a linked instance…but the logic doesn't (insert, del, or update) the trigger table…actually the logic doesn't use the table at all. But, I've have identified the table and triggers.

Error Message from the failed job:

  Message
    Executed as user: **********. ...because the server option "disallow_results_from_triggers" is true. 
    [SQLSTATE 01000] (Message 531)  Cannot set NOCOUNT to OFF inside the trigger execution because the server option "disallow_results_from_triggers" is true. 
    [SQLSTATE 01000] (Message 531)  Cannot set NOCOUNT to OFF inside the trigger execution because the server option "disallow_results_from_triggers" is true. 
    [SQLSTATE 01000] (Message 531)  Warning: Null value is eliminated by an aggregate or other SET operation. 
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. 
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. 
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. 
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. 
    [SQLSTATE 01003] (Message 8153)  Warning: Null value...  The step failed.

Wait and you shall receive….this is the 1st this happened after my last edit

Message

Executed as user:********. ...because the server option "disallow_results_from_triggers" is true. 

[SQLSTATE 01000] (Message 531)  Warning: Null value is eliminated by an aggregate or other SET operation. 
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. 
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. 
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. 
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. 
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. 
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. 
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. 
[SQLSTATE 01003] (Message 8153)  Warning...  The step failed.

Steps to solve the problem…
1. Found all triggers in instances with SET NOCOUNT OFF inside triggers and comment out the line….using the script provided in this thread
2. Ran the job for weeks with no errors until today (7/16/2014 5:30 AM).

Best Answer

Find the triggers that have SET NOCOUNT OFF and fix them. I can't possibly imagine what good can come from having that line anywhere. NOCOUNT should be ON in every single module on your server.

This will give you the definition (and may introduce some false positives, so check closely), you'll just need to add GO between them and change CREATE to ALTER:

SELECT 'GO
-- ' + QUOTENAME(s.name) + '.' + QUOTENAME(m.name) + '
GO',
  m.definition
  FROM sys.triggers AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  INNER JOIN sys.sql_modules AS m
  ON t.[object_id] = m.[object_id]
  WHERE LOWER(m.definition) LIKE N'%set%nocount%off';

You may need to repeat this task in multiple databases.

Once you've fixed all of those, try the job again. If the error continues happening, try to narrow down the trigger(s) responsible and inspect the definition more closely. You may have to reverse engineer the job and all of the things it calls in order to find it, but once you have, post it here.

Related Question