Lost New Line Character Error Described

Question

Lost New Line Character Error Described

dbuendiab

SSC Eights!

Points: 993
More actions
November 9, 2010 at 7:03 am

#231103

(Already posted in General 2K5 Forum, posted again here - maybe more adequate place)
I've been near to send a post to the world on this issue I've been fighting with for a couple of weeks, but at last my coworker gave with this subtle problem and now I want to share it with anyone who could get profit of it.
I have a big SP centralizing more than a hundred another SPs calls. Typically, the code seems something like:
[font="Courier New"]Create Proc MainProc
AS
-- First step comment ----
EXEC dbo.Proc01
-- Second step comment ----
EXEC dbo.Proc02
-- Third step comment ----
EXEC dbo.Proc03
-- and so on...[/font]
With this not very sophisticated code, it's very strange that Proc02 it's not being executed. My internal tracing shows info from Proc01, and then from Proc03, but nothing about Proc02.
Having heard about memory corruptions somewhere, dropped down the SPs and recreated all of them. No success.
The Hole Grial of IT: shut down, restart server. The damned thing again.
But the strangest thing is that if I execute the Procs one by one they got executed, not only Proc01 and Proc03, but Proc02 as well.
Where was the problem?
My coworker got an hex dump of source code, and here is what he got:
[font="Courier New"]....
00000080h: 2D 2D 0D 45 78 65 63 20 64 62 6F 2E 41 46 5F 30 ; --.Exec dbo.AF_0
....[/font]
Usual combination is 0D 0A (CR LF, CHAR(13) + CHAR(10)), but some way CHAR(10) is not there, and what happens is you are seeing a new line in QA but the SQL parser does not, so what it sees is:
[font="Courier New"]-- Second step comment ----EXEC dbo.Proc02[/font]
I.E., the answer is that EXEC command is commented although you see it in a new line:
[font="Courier New"]-- Second step comment ----
EXEC dbo.Proc02[/font]
I must say this problem arose with SQL Server 2005 Management Studio Query Analyzer, although I don't know for now if it's relevant or not. May be some kind of soft CR...
I also want to remark SQL code is not positional, so CRLF position is not significant... Except for one-line comments, where we've found this anomaly.
Diego Buendía
BCN, SPAIN

Viewing 3 posts - 1 through 2 (of 2 total)

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

AmarDeepthi SSC Enthusiast Points: 118 More actions · Answer 1

Hi,

This is right. So the question posted should have answer, 1,2,3,4 as we have an explicit new line character(char(13) ) introduced.

If we exclude, char(13) then it results in 1, 3, 4 as said.

The question for the answer given should be something like this.

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 '

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

dbuendiab (11/9/2010)
(Already posted in General 2K5 Forum, posted again here - maybe more adequate place)
I've been near to send a post to the world on this issue I've been fighting with for a couple of weeks, but at last my coworker gave with this subtle problem and now I want to share it with anyone who could get profit of it.
I have a big SP centralizing more than a hundred another SPs calls. Typically, the code seems something like:
[font="Courier New"]Create Proc MainProc
AS
-- First step comment ----
EXEC dbo.Proc01
-- Second step comment ----
EXEC dbo.Proc02
-- Third step comment ----
EXEC dbo.Proc03
-- and so on...[/font]
With this not very sophisticated code, it's very strange that Proc02 it's not being executed. My internal tracing shows info from Proc01, and then from Proc03, but nothing about Proc02.
Having heard about memory corruptions somewhere, dropped down the SPs and recreated all of them. No success.
The Hole Grial of IT: shut down, restart server. The damned thing again.
But the strangest thing is that if I execute the Procs one by one they got executed, not only Proc01 and Proc03, but Proc02 as well.
Where was the problem?
My coworker got an hex dump of source code, and here is what he got:
[font="Courier New"]....
00000080h: 2D 2D 0D 45 78 65 63 20 64 62 6F 2E 41 46 5F 30 ; --.Exec dbo.AF_0
....[/font]
Usual combination is 0D 0A (CR LF, CHAR(13) + CHAR(10)), but some way CHAR(10) is not there, and what happens is you are seeing a new line in QA but the SQL parser does not, so what it sees is:
[font="Courier New"]-- Second step comment ----EXEC dbo.Proc02[/font]
I.E., the answer is that EXEC command is commented although you see it in a new line:
[font="Courier New"]-- Second step comment ----
EXEC dbo.Proc02[/font]
I must say this problem arose with SQL Server 2005 Management Studio Query Analyzer, although I don't know for now if it's relevant or not. May be some kind of soft CR...
I also want to remark SQL code is not positional, so CRLF position is not significant... Except for one-line comments, where we've found this anomaly.
Diego Buendía
BCN, SPAIN

Thanks for posting this, Diego... I've run into similar and other anomolies, as well. People think I'm nuts when I make them retest after changing just a comment in the code. As we can see, they sometimes DO matter. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)