Subtle Line Feed / Carriage Return issue

  • Comments posted to this topic are about the item Subtle Line Feed / Carriage Return issue

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

    Thanks

  • 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]

  • This was removed by the editor as SPAM

  • 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 πŸ™

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

    πŸ™

  • 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)

  • @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

  • 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)

  • 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

  • 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
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • 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 πŸ˜‰

  • 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.

  • 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
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • 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.

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

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