Subtle Line Feed / Carriage Return issue

Question

Post reply

Subtle Line Feed / Carriage Return issue

dbuendiab

SSC Eights!

Points: 993
More actions
December 22, 2010 at 9:32 pm

#388492

Comments posted to this topic are about the item Subtle Line Feed / Carriage Return issue
December 23, 2010 at 12:08 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 66 total)

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

I have executed the script - output is 1,2, 3, 4.

Thanks

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

After being told that I had selected the wrong answer, I copied the code to SSMS for SQL 2005, and to SSMS SQL 2008 ....

In BOTH 2005 and 2008 I DID NOT get what was deemed to be the correct answer.

Further the support for the so called correct answer is a sole forum posting to which NO ONE respond to .....AUGHHHHHHHHHH

Now I will not say what the correct answer is, but for sure it is not what the question says it is.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ziangij SSCrazy Eights Points: 8066 More actions · Answer 3

Hardy21 (12/22/2010)
I have executed the script - output is 1,2, 3, 4.

even i got the same answer... lost my point today 🙁

AJN SSC Veteran Points: 205 More actions · Answer 4

So I lost my point today because I have a fully patched and up to date instance of SQL?

🙁

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

It's not a bug: CR is carriage return, so the line is the same.

The line 2 is not printed beacuse the print command is joined to the commented line.

So, change the single line comment with /**/. All lines will be printed.

declare @sql varchar(100)

declare @cr char(1)

declare @NL char(1)

set @cr = char(13)

set @NL = char(10)

set @sql =

'print 1' + @cr + @NL +

'/* Comment one */' + @cr +

'print 2' + @cr + @NL +

'/* Comment two*/' + @cr + @NL +

'print 3' + @cr + @NL +

'/* Comment three*/' + @NL +

'print 4'

print '

What you see...'

print '-----------------------'

print @sql

print '

is not what you get!'

print '-----------------------'

exec (@sql)

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 6

@carlo: you are right.

But as per the question, we should give answer for the question: What will you get executing this T-SQL code in Query Analizer (SQL2K) or Management Studio (2005)?

So, EXEC(@sql) result is 1,2,3,4.

Thanks

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 7

Hardy21 (12/23/2010)
@carlo: you are right.
But as per the question, we should give answer for the question: What will you get executing this T-SQL code in Query Analizer (SQL2K) or Management Studio (2005)?
So, EXEC(@sql) result is 1,2,3,4.

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)

Bobby VK SSC Enthusiast Points: 165 More actions · Answer 8

What is it with questions being wrong on this site?

SQL 2008 Developer Edition. I copied your code, pasted into my SSMS and this is the exact 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

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 9

AJN (12/23/2010)
So I lost my point today because I have a fully patched and up to date instance of SQL?
🙁

Agree. The SQL 2005 instances that we have are all running SP4 (because we need to certify our products against the latest & greatest). The SQL 2008 are also either SQL 2008 SP2 or R2 editions. In all editions, the answer is as Hardik indicated - 1,2,3,4.

I do believe that the question could have been more specific about using SQL 2005 RTM, and not the latest & greatest.

In any case, I learnt something new today (that when constructing SQL statements, one needs to be very specific and precise while working with CR/LF.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Bobby VK SSC Enthusiast Points: 165 More actions · Answer 10

Define a single var as char(2) with cr and lf in it, instead of chancing forgetting either when you put them in separate vars.

Question posters: Run your code and mention which version you have tested it on when you post it. Saves a lot of frustration.

Realistically though, why are we getting frustrated over fake points at a website, it's not like you can buy anything with them 😉

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

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.

Mi actual position is as developer. Some year ago I acted as DBA but then my company outsourced the SQL Server services and now we are using a hosting. I verified the code against SS2k and 2005, as stated in the question. I did not bother on service pack updates because I didn't think on that possibility.

The question, once isolated, was treated to focus the key point, that is, I had a SQL sentence with a line comment alone and the next line (valid SQL code) did not execute at all. It was no tricky code; only a damned line that did not want to run. I sent the code to my colleague, a very clever programmer, and he thought about examining the SQL text with an hex editor (it would not have crossed my mind in life), so he discovered the lacking character.

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.

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 12

dbuendiab (12/23/2010)
...
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.

Yes, the question definitely taught me to be more careful when I work with CR/LF. Thank-you very much for that.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

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

For those who propose alternative writtings to the sample code, you must consider that code is only a construction to verify the actual behavior of a query.

The original code was something like:

Select *

From pubs.dbo.authors

where

au_lname >= 'M'

-- Let's add a filter by state, and what woud you think it doesn't work at all?

and state = 'CA'If you execute this code, not dynamic, not complex, and the result set does contain authors from other states than California, maybe you get confused as I did.

And the reason is the CRLF behind the question mark was not there; in the hex editor there was only the LF character.

As Nakul Vachhrajani's post suggests, I'd like to remark that in SQL Server, unlike other programming languages, the organization of the code is generally irrelevant - but there is at least an exception: the line comment, and the (buggy) behavior I expose is a direct consequence of this exception.