Switching got me a problem

Asked By 0 points N/A Posted on -
qa-featured

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?

SHARE
Answered By 5 points N/A #118981

Switching got me a problem

qa-featured

Dear Friend

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.

Related Questions