Subtle Line Feed / Carriage Return issue

Question

Post reply

Subtle Line Feed / Carriage Return issue

Viewing 15 posts - 16 through 30 (of 66 total)

You must be logged in to reply to this topic. Login to reply

rkelsey 30734 Grasshopper Points: 14 More actions · Answer 1

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

dbuendiab SSC Eights! Points: 993 More actions · Answer 2

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.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

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

Mighty SSCrazy Eights Points: 9063 More actions · Answer 4

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

Carlo Romagnano SSC-Insane Points: 22601 More actions · Answer 5

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.

Toreador SSChampion Points: 11358 More actions · Answer 6

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 🙂

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 7

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 8

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.

ipounder SSCommitted Points: 1541 More actions · Answer 9

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!

valerygolub Grasshopper Points: 22 More actions · Answer 10

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

harendra.singh SSC Veteran Points: 287 More actions · Answer 11

harendra.singh

SSC Veteran

Points: 287

December 23, 2010 at 6:21 am

#1265884

Even i executed it to 1234..i dont waz went wrong...

TomThomson SSC Guru Points: 104773 More actions · Answer 12

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

SanDroid SSChampion Points: 10068 More actions · Answer 13

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:

crashdan SSCommitted Points: 1990 More actions · Answer 14

crashdan

SSCommitted

Points: 1990

December 23, 2010 at 7:11 am

#1265922

you can add me to the list

SanDroid SSChampion Points: 10068 More actions · Answer 15

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.