Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Why trigger is presenting this error "A trigger returned a resultset and / or running with SET NOCOUNT OFF while another outstanding result set was active."?

Proposed Answer Why trigger is presenting this error "A trigger returned a resultset and / or running with SET NOCOUNT OFF while another outstanding result set was active."?

  • Thursday, January 17, 2013 12:32 PM
     
     
    Sign In to Vote
    0
    I'm having trouble with the trigger when she performed shows this error "A trigger returned a resultset and / or running with SET NOCOUNT OFF while another outstanding result set was active."'m Using along with AnyDac to use the application in DelphiXe

    When placed the SET NOCOUNT ON at the beginning of the trigger works perfectly, my doubt is why you have to put this clause SET NOCOUNT ON and the same trigger works without this clause in other customers. Versions of SQL SERVER the problem that occurs is

    SQL SERVER 2008 10.0.1600.22 ((SQL_PreRelease) .080709-1414) and 10.50.1600.1

    SET NOCOUNT ON researched know that it disables the return of affected rows
     

All Replies

  • Thursday, January 17, 2013 1:05 PM
     
     
    Sign In to Vote
    0

    Have a look on below link...

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/5ee185e6-1980-4209-87f6-abeb9c5933fa/

    Thanks,

    saurabh

    http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html

     
  • Thursday, January 17, 2013 1:14 PM
     
     Proposed Answer
    Sign In to Vote
    1

    When placed the SET NOCOUNT ON at the beginning of the trigger works perfectly, my doubt is why you have to put this clause SET NOCOUNT ON and the same trigger works without this clause in other customers. Versions of SQL SERVER the problem that occurs is

    This is not an issue with the SQL Server version but the client API you are using.  Some APIs, such as ADO classic using OLE DB, return rowcount messages as empty resultsets.  Turning on SET NOCOUNT ON suppresses these DONE_IN_PROC messages that otherwise interfere with application resultset processing.  I can't speak to DelphiXe but most languages/APIs allow you to use a method like NextResultset to bypass unwanted results as an alternative to SET NOCOUNT.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

     
  • Thursday, January 17, 2013 10:51 PM
     
     
    Sign In to Vote
    0

    This is not an issue with the SQL Server version but the client API you are using. 

    Actually, this is SQL Server message 523, but I don't know in which context this error can occur. One guess is that it is related to MARS.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, January 18, 2013 4:56 AM
     
     
    Sign In to Vote
    0

    This is not an issue with the SQL Server version but the client API you are using. 

    Actually, this is SQL Server message 523, but I don't know in which context this error can occur. One guess is that it is related to MARS.

    Could be related to MARS, but still the error is still essentially due to interaction of the client API and the DONE_IN_PROC messages, right?  I think of DONE_IN_PROC messages being the responsibility of the client but what I don't know about MARS is a lot so I can't speak to the implications of MARS is enabled with SET NOCOUNT OFF.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

     
  • Friday, January 18, 2013 9:23 AM
     
     
    Sign In to Vote
    0

    There has to be something more. SQL Server puts the result sets in the output buffer. Maybe it is one of the API cursors, you know sp_cursorfetch and the guys? The message indicates that SQL Server is generating a result set, and in the midst of this, the trigger starts to produce reslut sets (well DONE_IN_PROC) and SQL Server realise that this confuse the API and produces this error?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se