January 7, 2015 at 10:35 pm
Comments posted to this topic are about the item Error messages
January 8, 2015 at 1:08 am
Another bad worded qotd:
My result.
Msg 515, Level 16, State 2, Line 7
Impossibile inserire il valore NULL nella colonna 'b' della tabella 'tempdb.dbo.#a__________________________________________________________________________________________________________________000000000211'. La colonna non ammette valori Null. INSERT avrà esito negativo.
L'istruzione è stata interrotta.
The Issue
NULL
January 8, 2015 at 2:08 am
This was removed by the editor as SPAM
January 8, 2015 at 5:11 am
Very good question, IMO.
Thanks
---------------
Mel. 😎
January 8, 2015 at 5:11 am
I think the crux of the question is what would happen if ERROR_MESSAGE() is used outside the context of a TRY... CATCH... block (i.e. nothing)
Exactly.
---------------
Mel. 😎
January 8, 2015 at 5:43 am
Good question. I think it effectively illustrates the use of the function. Thanks for it.
January 8, 2015 at 6:02 am
Awesome question, learn something new every day.
January 8, 2015 at 6:23 am
It is indeed a good question. However, I think the requirement of a primary key was a bit misleading as it can also occur when the field is simply "NOT NULL" as evidenced as follows:
CREATE TABLE #mytable (
myid tinyint NOT NULL,
mychar char(1) NULL,
);
INSERT #mytable ( myid, mychar )
VALUES ( 1, 'A' ), ( 2 , 'B'), ( NULL, 'C' ), ( 4, 'D' ), ( NULL, 'E' ), ( 6, 'F' );
SELECT ERROR_MESSAGE() AS 'The Issue';
The key to the correct response is the reference to the result set.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
January 8, 2015 at 9:02 am
I like the question. Next time I will read it all the way through before answering 😛
January 8, 2015 at 9:23 am
Gosh - failed!
I have to read more carefully the options 🙂
I was focused on the error of the INSERT statement but didn't realize that the result of ERROR_MESSAGE() was asked.
My lesson learned: Read carefully the answers!
BTW: Very interesting and good question.
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
January 8, 2015 at 11:18 am
Good question.
But the first option wasn't possible even if ERROR_MESSAGE() could function outside a catch block, because if it did function it would produce the error message ("Cannot insert the value NULL into column 'myid', table 'somedb.someschema.mytable'; column does not allow nulls. INSERT fails.") and not what would get put into the message window before the actual error message if the error occurred outside a TRY block. So there was no need to know that ERROR_MESSAGE wouldn't operate outside a catch block to eliminate the first option.
Tom
January 9, 2015 at 12:52 am
Thank you for the post, really good one.
(even after the proper analysis, I ended up "selecting" the wrong answer. I "guess" the problem lies between the keyboard and the chair. :-))
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
January 9, 2015 at 2:55 am
Great QoTD! Thanks.
Two concepts were revisited at the same time.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
January 10, 2015 at 10:45 pm
Stewart "Arturius" Campbell (1/8/2015)
Interesting questionIt has to be read carefully.
Then remember whether the PK violation is a batch terminating error or not.
I think the crux of the question is what would happen if ERROR_MESSAGE() is used outside the context of a TRY... CATCH... block (i.e. nothing)
+1, good explanation, thanx, I think that was the trick in the question.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 11, 2015 at 8:24 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply