Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Subtle Line Feed / Carriage Return issue


Subtle Line Feed / Carriage Return issue

Author
Message
Nakul Vachhrajani
Nakul Vachhrajani
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1824 Visits: 2127
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
Google Plus: +Nakul
Bobby VK
Bobby VK
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 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 Wink
dbuendiab
dbuendiab
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 218
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
Nakul Vachhrajani
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1824 Visits: 2127
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
Google Plus: +Nakul
dbuendiab
dbuendiab
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 218
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.
rkelsey 30734
rkelsey 30734
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 9
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
dbuendiab
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 218
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
Hardy21
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1399
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
Mighty
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4177 Visits: 1648
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
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3626 Visits: 3236
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search