N/APosted on - 04/15/2012
I switched from MS SQL 2000 to 2008 and got an error message, “A trigger returned a result set and/or was running with SET NOCOUNT OFF while another outstanding result set was active.”
What do I need to do in order to fix it?
Switching got me a problem
Try below solutions
Put SET NOCOUNT ON on top follows by SET NOCOUNT OFF toward the end of the trigger because if any result set is active it does contain a SET NOCOUNT OFF, but does not contain SET NOCOUNT ON.
The ability to return a result set from triggers is removed from future version of SQL server so this will lead to above problem.
The another solution is that whatever statement is creating a result set should either be removed or prefixed by INSERT INTO temp table to swallow the result.
You can use disallow result from triggers option to control the result set of trigger.