Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Subtle Line Feed / Carriage Return issue Expand / Collapse
Author
Message
Posted Thursday, December 23, 2010 2:22 AM


Default port

Default portDefault portDefault portDefault portDefault portDefault portDefault portDefault port

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 4:53 AM
Points: 1,433, Visits: 1,845
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
Post #1038650
Posted Thursday, December 23, 2010 2:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 10, 2012 7:49 AM
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 ;)
Post #1038652
Posted Thursday, December 23, 2010 2:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:41 AM
Points: 77, 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.


Post #1038653
Posted Thursday, December 23, 2010 2:34 AM


Default port

Default portDefault portDefault portDefault portDefault portDefault portDefault portDefault port

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 4:53 AM
Points: 1,433, Visits: 1,845
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
Post #1038654
Posted Thursday, December 23, 2010 2:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:41 AM
Points: 77, 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.
Post #1038657
Posted Thursday, December 23, 2010 2:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 1, 2011 7:53 AM
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
Post #1038661
Posted Thursday, December 23, 2010 2:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:41 AM
Points: 77, 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.
Post #1038662
Posted Thursday, December 23, 2010 3:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 4:43 AM
Points: 1,130, Visits: 1,391
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
Post #1038664
Posted Thursday, December 23, 2010 3:11 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:22 AM
Points: 3,291, Visits: 1,313
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


Post #1038667
Posted Thursday, December 23, 2010 3:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 2,560, Visits: 2,419
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.
Post #1038671
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse