April 1, 2014 at 10:22 pm
Comments posted to this topic are about the item A Questionable Trigger
April 2, 2014 at 1:10 am
Tricky again :doze: Some important information missing
4 obvious choices and then you have to :Whistling: guess which one of remaining correct options was taken for the right one :pinch:
April 2, 2014 at 1:10 am
Day by day QotD becomes difficult.
While executing update we didn't receive any result set; we got message only!!!
I guess there is difference it result set and message.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 2, 2014 at 1:12 am
dawryn (4/2/2014)
Tricky again :doze: Some important information missing4 obvious choices and then you have to :Whistling: guess which one of remaining correct options was taken for the right one :pinch:
+1
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 2, 2014 at 1:24 am
In sqlserver 2008 r2 the update doesn't return any resultset and no error message.
(2 row(s) affected)
(2 row(s) affected)
What I'm missing?
April 2, 2014 at 1:25 am
4/5 isn't bad. especially when the last one was the trick question. There was no set showplan on the query and nothing to say that you were trying to bringing back the execution plan.
April 2, 2014 at 1:31 am
I do not like question too long. I have just few minute to answer.
A lot of useless words and none of the main one.
April 2, 2014 at 3:03 am
Carlo Romagnano (4/2/2014)
In sqlserver 2008 r2 the update doesn't return any resultset and no error message.(2 row(s) affected)
(2 row(s) affected)
What I'm missing?
You are missing nothing. The update works fine and it doesn't return a resultset.
It can be however, that the software client used to test the update, can't handle the fact that the update send 2 messages with "(2 row(s) affected)". SQL Server Management Studio has no problem with that.
And as stated by someone before, a message that is returned, is not a resultset.
April 2, 2014 at 3:18 am
This was removed by the editor as SPAM
April 2, 2014 at 3:29 am
Carlo Romagnano (4/2/2014)
In sqlserver 2008 r2 the update doesn't return any resultset and no error message.(2 row(s) affected)
(2 row(s) affected)
What I'm missing?
I have executed in SQL 2012 and got the same message. No error message.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 2, 2014 at 3:34 am
free_mascot (4/2/2014)
Carlo Romagnano (4/2/2014)
In sqlserver 2008 r2 the update doesn't return any resultset and no error message.(2 row(s) affected)
(2 row(s) affected)
What I'm missing?
I have executed in SQL 2012 and got the same message. No error message.
Press ctrl+m before execute!
April 2, 2014 at 4:27 am
Andy, can you display the results that you get, including your error and can you tell which tool and version you are using to execute the statements?
As I said before, for me there are no errors, and there is no result set retuned, only two messages.
If I would switch on the option to return the execution plan, than in fact, it is returning two result sets. The first for the initial update, the second for the update that happened inside the trigger.
April 2, 2014 at 4:59 am
Mighty (4/2/2014)
Andy, can you display the results that you get, including your error and can you tell which tool and version you are using to execute the statements?As I said before, for me there are no errors, and there is no result set retuned, only two messages.
If I would switch on the option to return the execution plan, than in fact, it is returning two result sets. The first for the initial update, the second for the update that happened inside the trigger.
Error comes when the following is the setting
sp_configure 'disallow results from triggers', 1
reconfigure
Include Actual Execution Plan (Ctrl+M), then you will able to see this message.
Msg 524, Level 16, State 1, Procedure updateQuestions, Line 4
A trigger returned a resultset and the server option 'disallow results from triggers' is true.
By default it set to 0.
For more details disallow results from triggers Server Configuration Option
April 2, 2014 at 5:34 am
Sorry but this QotD was too confusing. 🙂
Nowhere does its author states that he has execution plan options enabled.
And an UPDATE command returns no resultset (unless we use the OUTPUT clause).
By default, it returns only a message of rows affected when one does not use "SET NOCOUNT ON" as stated in one of the answers.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
April 2, 2014 at 6:12 am
free_mascot (4/2/2014)
dawryn (4/2/2014)
Tricky again :doze: Some important information missing4 obvious choices and then you have to :Whistling: guess which one of remaining correct options was taken for the right one :pinch:
+1
+1. The result set was dependent on the execution plan and, while it generates a result set, it doesn't return one. The last one was a guess because of what was unsaid.
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply