Switching got me a problem

Asked By Jimbo Kramer 0 points N/A Posted 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?

• Status: Open
• Question Views: 1317
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: Windows 2008

Answered By Harold kenosi 5 points N/A #118981

Switching got me a problem

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.

About Harold kenosi

Questions
3

Answers
36

Best Answers
8

• Vote Up 0 Vote Down

• Posted on - 04/15/2012
• Question Category: Windows 2008