possible bug regarding single line comments in SQL Server Management Studio

I don't know if this is a bug or something I've messed up in my settings. I am using SQL Server Management Studio with SQL 2005 service pack 1 installed, and I've been using it successfully ever since SP1 came out. All of a sudden, any time I have a query with a single line comment (i.e. using dash dash -- ), the rest of the query is ignored as if the single line comment is really a multi-line comment without the closing */

An example:

select top 100 *

from myTable

-- this is a comment

where myTableID = 1234

This would normally return exactly one row, but instead returns 100 rows ( as if the where clause did not exist). If I move the where clause above the comment, it works as expected. Any ideas?

In trying to fix this, I've uninstalled and reinstalled and even reinstalled SP1, but it still isn't fixed.

You've got me stumped. I took the code you have posted and it ran fine for me.

Can you post the exact code that you are using? Maybe we'll see that hanging character you missed or somthing.

|||

I used the exact query, merely changing the table name and ID column.

I ended up going through the process yet again (uninstalling the tools, reinstalling the tools, then installing SP1) and rebooted but still had the problem. However, after a second reboot, it starting behaving properly again. I hope my problem does not resurface.

|||We (me a colleague of mine), have withnessed this same behaviour and, atfer some deep analysis, we have concluded that this bug arises when the Transact-Sql line terminates with a CR (Carriage Return, Ascii(13)) only charaters when it should terminate with both CR (Carriage Return, Ascii(13)) followed by an LF (Line Feed, Ascii(10))

It seems that once SqlServer reachs a single-line comment identifier (--) it keeps fetching bytes untill a compounded line termination (CR followed by LF) is reached.

In the following sample, the {CR+LF} and {CR} are representations of their respective invisible ascii codes.

--This is just a normal single-line comment {CR+LF}
SELECT 'This command works fine' {CR+LF}

--This is a buggy single-line comment {CR}
SELECT 'This command does not work'{CR}
SELECT 'This command does not work either' {CR+LF}
SELECT 'This command work fine too' {CR+LF}

Moreover we have reasons to bellieve that this bug resides in Query Editor provided with SQL Server Management Studio Express Edition (at least, this was version my colleague was using when this bug arose). Anyway, although this bug must be corrected, LF should just be ignored when dealing with Sql scripts.

Running software:

SQL Server Management Studio Express Edition

Ms SQLServer 2000 (8.0.2039)

Win XP Portuguese SP2

Getting this sample you can:

- Load and execute it, using QueryAnalyzer to see the refered behaviour;
- View it, using Notepad, to see what actually happened.