November 9, 2010 at 6:01 am
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(13) 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.
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
November 9, 2010 at 6:37 am
Errata: what is lacking is 0A (line feed, NL , CHAR(10)), not carriage return 0D.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply