December 23, 2010 at 2:53 am
in SQL 2k5 Management Studio
What you see...
-----------------------
print 1
-- Comment one
print 2
-- Comment two
print 3
-- Comment three
print 4
is not what you get!
-----------------------
1
2
3
4
December 23, 2010 at 2:55 am
By the way, my cleaver colleague reminds me we had got this behavior sometimes, when sending SQL code in Lotus Notes emails.
Fortunately, in those cases the behavior led to a syntax error, forcing us to rewrite the phrase, and possibly introduce new CRLF pairs that disabled the malformed ones.
But in this one, as in the sample of my last post, there is no syntax error, but a SQL phrase that doesn't work as expected.
December 23, 2010 at 3:02 am
dbuendiab (12/23/2010)
It's a very disturbing behavior, and, looking the answers, those of you who complain has the issue addressed with the correct update, but I have seen a lot more of readings without further comments, and I guess maybe there has been someone who has got the same answer.Anyway, I admit it was a sure-you-lose-question. I apologize for this to all of you who have got disappointed, and by the other side, I hope it can help someone to avoid getting so confused as I got.
We have also faced this type of issue in past but it was due to source code versioning/maintenance tool. We spent almost 2 days to resolve the issue and then came to know the issue when we open the file in Hex editor.
Because you have not mentioned SQLServer version, almost all thinking latest version of SQL Server.
Thanks
December 23, 2010 at 3:11 am
Carlo Romagnano (12/23/2010)
If you get 1,2,3,4
that means that in the script you do not report single line comments.
The script in @sql looks like this:
PRINT 2 is joined to the upper line.
set @sql ='print 1
-- Comment one print 2
-- Comment two
print 3
-- Comment three
print 4
'
exec (@sql)
That is not what you see in SQL Query Analzer. The output actually is
What you see...
-----------------------
print 1
-- Comment one
print 2
-- Comment two
print 3
-- Comment three
print 4
is not what you get!
-----------------------
1
3
4
December 23, 2010 at 3:15 am
Some tools (powerbuilder,qlick) replace CR and LF with space, so one line comment
in the script could alter the command.
Running the script in sql2000 I get 1,3,4 but in sql2005 + sp I get 1,2,3,4.
Running script in PowerBuilder I get just a comment
-- comment 1 print 1 -- comment 2 print 2, etc.
December 23, 2010 at 3:18 am
I didn't even notice it said SQl2000/2005 - somehow I always assume that questions will be targetted at the latest release.
I must admit that I ran the script before answering - I couldn't see any reason that the result woukldnb't be 1,2,3,4 so wasnted to see what I was missing. And of course I did get 1,2,3,4 🙂
December 23, 2010 at 4:02 am
dbuendiab (12/23/2010)
I'm sorry for your lost points, guys. If it's a consolation for you, I spent two mornings completely puzzled with this issue, to the point I asked for help in the forums an lately, when I discovered the -subtle- question, felt it was worth to broadcast it all along.
I don't care about points. I do care about learning from the daily questions. In this case, I don't feel I learned anything useful.
SQL Server 2000 has been out of support for a long time already. SQL Server 2005 is still supported - that support mainly consists of regular service packs. Install them - they are there for a reason. If you find a bug in the latest SP of SQL 2005, SQL 2008, or SQL 2008 R2, then you have a good QotD.
As to your lost morings - I'm sorry. I can't give you back that time. But you can at least make proper use of it - use this in your argument to management to make a case for patching your SQL 2005 servers to the latest SP, and to upgrade the SQL 2000 servers. Maybe even move all servers to SQL 2008 R2, since SQL 2005 support won't last very long anymore. Installing service packs and moving off of versions that are beyond their end of support will save you from many other bugs too, that might well cost you more than just two mornings of head scratching.
December 23, 2010 at 4:30 am
Of course I got it wrong as probably all others trying this question. And it does not matter if you try to answer with or without SSMS.
QotD should try to teach us something. This one doesn't and that's already bad as such. The wrong answer options make it even worse.
I want my point back!!! :crying:
Best regards,
Dietmar Weickert.
December 23, 2010 at 4:30 am
The answer I got is 1,2,3,4. Running through SSMS 2008 R2 to an instance of SQLServer 2005.
Seems like nobody tested this properly! That's the real answer that should be taken on board.
I know it's not exactly business critical but it's also not exactly hard to make sure the answer is correct.
OK back to real life now!
December 23, 2010 at 5:32 am
So do I. (SQL 2008 Developer Edition)
Output:
What you see...
-----------------------
print 1
-- Comment one
print 2
-- Comment two
print 3
-- Comment three
print 4
is not what you get!
-----------------------
1
2
3
4
December 23, 2010 at 6:21 am
Even i executed it to 1234..i dont waz went wrong...
December 23, 2010 at 6:57 am
stewartc-708166 (12/23/2010)
Having investigated this further, I have found that the "correct" answer occurs on SQL2000 and SQL2005 RTM.this was registered as a bug and repaired with CU2 of SQL2005 (also included in service pack 1).
So MS had it right (CR does not terminate a -- comment) and some nitwit reported this as a bug and MS was sufficiently stupid to "fix" it? Oh mo chreach!
Tom
December 23, 2010 at 7:02 am
stewartc-708166 (12/23/2010)
Having investigated this further, I have found that the "correct" answer occurs on SQL2000 and SQL2005 RTM.this was registered as a bug and repaired with CU2 of SQL2005 (also included in service pack 1).
Guess what. It also works correctly on an up to date instance of SQL 2000.
This is a PERFECT example of why, in my world, the patch level is checked and corrected on all instances of SQL server during any code update. 😎
How else could you expect code to execute uniformly in your enterprise environment? :w00t:
December 23, 2010 at 7:11 am
you can add me to the list
December 23, 2010 at 7:16 am
Dietmar Weickert (12/23/2010)
Of course I got it wrong as probably all others trying this question. And it does not matter if you try to answer with or without SSMS.QotD should try to teach us something. This one doesn't and that's already bad as such. The wrong answer options make it even worse.
I want my point back!!! :crying:
I want my point back too.
I have seen the behavior the author was trying to replicate serveral time in my career and in school.
It seem there where always two common factors that caused it.
1. The code was cut and pasted from another source directly into a white space editor.
2. The code was compiled, and sometimes deployed, without testing.
This was real common in c/c++ class when people cut and pasted someone elses answers into thier homework and turned it in without checking if the output was correct.
Viewing 15 posts - 16 through 30 (of 66 total)
You must be logged in to reply to this topic. Login to reply