Is it SQL compiler defect

Question

Is it SQL compiler defect

Gopal-49563

Ten Centuries

Points: 1209
More actions
September 29, 2004 at 9:48 am

#63439

First is it possible to see different result from SQL Query analyzer when you have a line of comment in your query and after removing this comment line?
Answere is YES. I have been seeing this issue with one query. I wonder if anyone else experienced it ever.
DECLARE @START_TIME DATETIME
DECLARE @END_TIME DATETIME
DECLARE @sort_log_id INT
select @sort_log_id = max(id) from MYDB.DBO.sort_log
--uu
IF @sort_log_id IS null
BEGIN
print '****isnull*******'
END
Above query results
****isnull*******
Now remove the commented line --uu and the reult will be
The command(s) completed successfully.
Anybody has any explanation!!!!!
Note: It doesn't matter the content of the table. I always had at least one record in this table.

Viewing 6 posts - 1 through 5 (of 5 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Just ran the code from above and never received the *****ISNULL****

I would however change the code to be IF ISNULL(@sort, 0) = 0 and instead of SELECT @sort = MAX(ID) to be SET @sort = (SELECT MAX(ID))

don't know why you are getting the weird results. Was unable to duplicate. Sorry.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Gopal-49563 Ten Centuries Points: 1209 More actions · Answer 2

I am not sure what to check but I noticed that it happens only from one box even though the SQL Server is same.

Any Guess where could be the issue. Can it be SQL driver issue? I think Query Analyzer establishes connection using OLEDB.. could there be some version issue? Has anybody seen this kind of behaviour before?

Gopal

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 3

There is a bug in SQL Server (I noticed it in SQL 7 and SQL 2000 SP2 and SP3) that is seen in EM and in QA. Stored Procedures are stored in syscomments and so stored in 4k blocks (or 8k?). The bug occurs when a carriage return/line feed (CR/LF) spans the division such that the CR is the last byte of a line and the LF is the first byte of the next line. The problem is that SQL Server drops the LF on the next line resulting in a CR without a trailing LF. Since the LF is what truely moves to the next line of code, the next line of code is commented out. However, in the SP editor in EM and in QA they portray a new line due to the stranded CR and so it appears (and is highlighted accordingly) that the next line is NOT commented out even though the compiled code has the next line commented out. Now for the caviate. If the CR still remains as the very last character on the first of those lines the compiled code DOES correctly treat it as a CF/LF. But, should you then edit the SP to add or remove even one byte from the SP prior to that line break the stranded CR moves from the last position in the syscomments row to the middle of that or another row which reveals the problem. This issue is exacerbated by the bug within QA that prepends a CR/LF prior to the ALTER PROCEDURE line, appends a CR/LF just prior to the GO, and inserts an additional space between the ALTER PROCEDURE key words (it replaces the first occurance of "CREATE" with "ALTER " noting the space). This results in the CR/LF combos from being steadilly pushed down every time you edit a procedure even if you are functionally only appending code to the procedure.

If you wish to see proof of the CR/LF bug simply export the proc (or Save the QA text) to a text file and view it with a hex editor. Alternatively you can look at the ASCII codes of each character in syscomments for the SP. Should you not find a non-paired CR/LF in the exported SP then there is some other problem. Please post what you find. I hope this helps.

Incidentally, to fix the problem backspace out the corrupted CR/LF and press the ENTER key to restore a propper CR/LF. One of these days I'll create a job or script that scans the catalog for this issue. Every now and then we get burned on this (such as an AND condition suddenly being ignored). We've been lucky enough so far that it hasn't happened in a DELETE statement or some other tragic situation.

Gopal-49563 Ten Centuries Points: 1209 More actions · Answer 4

You are correct Aaron. I did see a special character just before the beginning of next line after commented line and this must have caused this kind of behaviour.

But I do not know how somebody can manage to insert special character like this. Though my query was a direct SQL query but even Stored procedures may have this kind of issue as you described. I am sure this might pose any serious issue in certain situations.

I will try to research further on this thing.

Gopal

Hans Lindgren SSChampion Points: 10496 More actions · Answer 5

I also believes that it happends when you get some files from UNiX, since the CrLfs are often stored differently. Try UltraEdit and do a conversion and see if that also solved your problem. (I have not encountered the problem, mentioned before, with SPs (yet??))

Happy hunting!