Incorrect query results with embedded comments

The following two identical queries return different
results. The first one with comments embedded in the query
returns all rows (ignoring the condition after the
comment). The second query with no comment works fine.
My SQL Server version is:
Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug 26
2002 15:09:48 Copyright (c) 1988-2000 Microsoft
Corporation Enterprise Edition on Windows NT 5.2 (Build
3718: )
--
select CLEC_EU_DISCONNECT_INFORMATION.DNUM
CLEC_EU_DISCONNECT_INFORMATION_DNUM,
CLEC_TXN_PON.PON_VER
CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
left outer join CLEC_EU_DISCONNECT_INFORMATION
on
CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATION
.DISC_INFO_PON_VER
and
CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DISC
_INFO_LOCNUM
--left outer join CLEC_EU_TRANSFER_CALLS on
--
CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TRA
NSFER_CALLS.TC_LOCNUM
--and
CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_TR
ANSFER_CALLS.TC_PON_VER
--and
CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS.
TC_DNUM
where CLEC_TXN_PON.PON_VER='217-00'
select CLEC_EU_DISCONNECT_INFORMATION.DNUM
CLEC_EU_DISCONNECT_INFORMATION_DNUM,
CLEC_TXN_PON.PON_VER
CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
left outer join CLEC_EU_DISCONNECT_INFORMATION
on
CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATION
.DISC_INFO_PON_VER
and
CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DISC
_INFO_LOCNUM
where CLEC_TXN_PON.PON_VER='217-00'Shame this lost it's formatting - but I'm guessing all the
joins are on single lines.
I've come across this a couple of times where comments
mess up (or help) a query but never with v2000.
Look at syscomments to see what the query looks like.
Especially the line termination character on the last line.
It's worthwhile just retyping the query too.
Are you using query analyser?
>--Original Message--
>The following two identical queries return different
>results. The first one with comments embedded in the
query
>returns all rows (ignoring the condition after the
>comment). The second query with no comment works fine.
>My SQL Server version is:
>Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug
26
>2002 15:09:48 Copyright (c) 1988-2000 Microsoft
>Corporation Enterprise Edition on Windows NT 5.2 (Build
>3718: )
>--
>
>select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>CLEC_TXN_PON.PON_VER
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>left outer join CLEC_EU_DISCONNECT_INFORMATION
>on
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
N
>..DISC_INFO_PON_VER
>and
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
C
>_INFO_LOCNUM
>--left outer join CLEC_EU_TRANSFER_CALLS on
>--
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
A
>NSFER_CALLS.TC_LOCNUM
>--and
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
R
>ANSFER_CALLS.TC_PON_VER
>--and
>CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
.
>TC_DNUM
>where CLEC_TXN_PON.PON_VER='217-00'
>
>select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>CLEC_TXN_PON.PON_VER
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>left outer join CLEC_EU_DISCONNECT_INFORMATION
>on
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
N
>..DISC_INFO_PON_VER
>and
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
C
>_INFO_LOCNUM
>where CLEC_TXN_PON.PON_VER='217-00'
>.
>|||Hi Swami,
Does the problem occur if you place the two queries in Query Analyzer? I
think the command is not correctly generated.
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--
| Content-Class: urn:content-classes:message
| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
| Subject: Incorrect query results with embedded comments
| Date: Wed, 2 Jul 2003 11:34:41 -0700
| Lines: 78
| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:22629
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| The query mentioned below was a programmatically generated
| query, with lines ending with "Carriage return" (char(13))
| character, and did not have line feed (char(10)). When
| such generated SQL was copied and pasted into SQL Query
| analyzer displayed perfectly fine, but did not recognize
| the break of the line.
|
|
| >--Original Message--
| >The following two identical queries return different
| >results. The first one with comments embedded in the
| query
| >returns all rows (ignoring the condition after the
| >comment). The second query with no comment works fine.
| >
| >My SQL Server version is:
| >
| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug
| 26
| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
| >Corporation Enterprise Edition on Windows NT 5.2 (Build
| >3718: )
| >
| >--
| >
| >
| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >CLEC_TXN_PON.PON_VER
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
|
| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >on
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| N
| >..DISC_INFO_PON_VER
| >and
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| C
| >_INFO_LOCNUM
| >--left outer join CLEC_EU_TRANSFER_CALLS on
| >--
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
| A
| >NSFER_CALLS.TC_LOCNUM
| >--and
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
| R
| >ANSFER_CALLS.TC_PON_VER
| >--and
| >CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
| .
| >TC_DNUM
| >where CLEC_TXN_PON.PON_VER='217-00'
| >
| >
| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >CLEC_TXN_PON.PON_VER
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
|
| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >on
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| N
| >..DISC_INFO_PON_VER
| >and
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| C
| >_INFO_LOCNUM
| >where CLEC_TXN_PON.PON_VER='217-00'
| >
| >.
| >
||||Here are two queries. Each one generates a SQL query. Copy
and paste the results of the query to the query pane of
query analyzer and run it. Both looks identical. The first
one does not work, but the second one does.
--
select 'select * ' + char(13) + '--comment--' + char(13)
+ 'from sysobjects'
select 'select * ' + char(13) + '--comment--' + char(10)
+ 'from sysobjects'
--
You are not considering a carriage return as a line break
in the server engine, but you are considering as a line
break for the query analyzer.
Swami
>--Original Message--
>Hi Swami,
>Does the problem occur if you place the two queries in
Query Analyzer? I
>think the command is not correctly generated.
>
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Regards,
>Bill Cheng
>Microsoft Support Engineer
>--
>| Content-Class: urn:content-classes:message
>| From: "Swami Muthuvelu" <swami@.mclsystems.com>
>| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
>| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
>| Subject: Incorrect query results with embedded comments
>| Date: Wed, 2 Jul 2003 11:34:41 -0700
>| Lines: 78
>| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
>| MIME-Version: 1.0
>| Content-Type: text/plain;
>| charset="iso-8859-1"
>| Content-Transfer-Encoding: 7bit
>| X-Newsreader: Microsoft CDO for Windows 2000
>| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==>| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>| Newsgroups: microsoft.public.sqlserver.server
>| Path: cpmsftngxa09.phx.gbl
>| Xref: cpmsftngxa09.phx.gbl
microsoft.public.sqlserver.server:22629
>| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>| X-Tomcat-NG: microsoft.public.sqlserver.server
>|
>| The query mentioned below was a programmatically
generated
>| query, with lines ending with "Carriage return" (char
(13))
>| character, and did not have line feed (char(10)). When
>| such generated SQL was copied and pasted into SQL Query
>| analyzer displayed perfectly fine, but did not
recognize
>| the break of the line.
>|
>|
>| >--Original Message--
>| >The following two identical queries return different
>| >results. The first one with comments embedded in the
>| query
>| >returns all rows (ignoring the condition after the
>| >comment). The second query with no comment works fine.
>| >
>| >My SQL Server version is:
>| >
>| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug
>| 26
>| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
>| >Corporation Enterprise Edition on Windows NT 5.2
(Build
>| >3718: )
>| >
>| >--
>| >
>| >
>| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>| >CLEC_TXN_PON.PON_VER
>|
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>|
>| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>| >left outer join CLEC_EU_DISCONNECT_INFORMATION
>| >on
>|
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
>| N
>| >..DISC_INFO_PON_VER
>| >and
>|
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
>| C
>| >_INFO_LOCNUM
>| >--left outer join CLEC_EU_TRANSFER_CALLS on
>| >--
>|
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
>| A
>| >NSFER_CALLS.TC_LOCNUM
>| >--and
>|
>CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
>| R
>| >ANSFER_CALLS.TC_PON_VER
>| >--and
>|
>CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
>| .
>| >TC_DNUM
>| >where CLEC_TXN_PON.PON_VER='217-00'
>| >
>| >
>| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
>| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
>| >CLEC_TXN_PON.PON_VER
>|
>CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
>|
>| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
>| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
>| >left outer join CLEC_EU_DISCONNECT_INFORMATION
>| >on
>|
>CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
>| N
>| >..DISC_INFO_PON_VER
>| >and
>|
>CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
>| C
>| >_INFO_LOCNUM
>| >where CLEC_TXN_PON.PON_VER='217-00'
>| >
>| >.
>| >
>|
>.
>|||Hi Swami,
I think the comment to be generated inside a single query causes the
problem. Could you make it generated before or after the query?
In addition, none of the query works on my side in Query Analyzer.
select 'select * ' + char(13) + '--comment--' + char(13)
+ 'from sysobjects'
select 'select * ' + char(13) + '--comment--' + char(10)
+ 'from sysobjects'
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Bill Cheng
Microsoft Support Engineer
--
| Content-Class: urn:content-classes:message
| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
<463701c340c8$99cc8460$a401280a@.phx.gbl>
<8zvyhVVQDHA.412@.cpmsftngxa09.phx.gbl>
| Subject: RE: Incorrect query results with embedded comments
| Date: Thu, 3 Jul 2003 07:06:21 -0700
| Lines: 155
| Message-ID: <0a1e01c3416c$47c51e80$a501280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcNBbEfCiVpFmLFfSJ+NOObv2qtfuw==| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:22750
| NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Here are two queries. Each one generates a SQL query. Copy
| and paste the results of the query to the query pane of
| query analyzer and run it. Both looks identical. The first
| one does not work, but the second one does.
|
| --
| select 'select * ' + char(13) + '--comment--' + char(13)
| + 'from sysobjects'
|
| select 'select * ' + char(13) + '--comment--' + char(10)
| + 'from sysobjects'
| --
|
| You are not considering a carriage return as a line break
| in the server engine, but you are considering as a line
| break for the query analyzer.
|
| Swami
|
|
|
| >--Original Message--
| >Hi Swami,
| >
| >Does the problem occur if you place the two queries in
| Query Analyzer? I
| >think the command is not correctly generated.
| >
| >
| >This posting is provided "AS IS" with no warranties, and
| confers no rights.
| >
| >Regards,
| >
| >Bill Cheng
| >Microsoft Support Engineer
| >--
| >| Content-Class: urn:content-classes:message
| >| From: "Swami Muthuvelu" <swami@.mclsystems.com>
| >| Sender: "Swami Muthuvelu" <swami@.mclsystems.com>
| >| References: <00b301c340c4$a033c1a0$a501280a@.phx.gbl>
| >| Subject: Incorrect query results with embedded comments
| >| Date: Wed, 2 Jul 2003 11:34:41 -0700
| >| Lines: 78
| >| Message-ID: <463701c340c8$99cc8460$a401280a@.phx.gbl>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| Thread-Index: AcNAyJnM9s4j20agSQe1sjFP5SfVvw==| >| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| >| Newsgroups: microsoft.public.sqlserver.server
| >| Path: cpmsftngxa09.phx.gbl
| >| Xref: cpmsftngxa09.phx.gbl
| microsoft.public.sqlserver.server:22629
| >| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| >| X-Tomcat-NG: microsoft.public.sqlserver.server
| >|
| >| The query mentioned below was a programmatically
| generated
| >| query, with lines ending with "Carriage return" (char
| (13))
| >| character, and did not have line feed (char(10)). When
| >| such generated SQL was copied and pasted into SQL Query
| >| analyzer displayed perfectly fine, but did not
| recognize
| >| the break of the line.
| >|
| >|
| >| >--Original Message--
| >| >The following two identical queries return different
| >| >results. The first one with comments embedded in the
| >| query
| >| >returns all rows (ignoring the condition after the
| >| >comment). The second query with no comment works fine.
| >| >
| >| >My SQL Server version is:
| >| >
| >| >Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
| Aug
| >| 26
| >| >2002 15:09:48 Copyright (c) 1988-2000 Microsoft
| >| >Corporation Enterprise Edition on Windows NT 5.2
| (Build
| >| >3718: )
| >| >
| >| >--
| >| >
| >| >
| >| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >| >CLEC_TXN_PON.PON_VER
| >|
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
| >|
| >| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >| >on
| >|
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| >| N
| >| >..DISC_INFO_PON_VER
| >| >and
| >|
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| >| C
| >| >_INFO_LOCNUM
| >| >--left outer join CLEC_EU_TRANSFER_CALLS on
| >| >--
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_LOCNUM=CLEC_EU_TR
| >| A
| >| >NSFER_CALLS.TC_LOCNUM
| >| >--and
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DISC_INFO_PON_VER=CLEC_EU_T
| >| R
| >| >ANSFER_CALLS.TC_PON_VER
| >| >--and
| >|
| >CLEC_EU_DISCONNECT_INFORMATION.DNUM=CLEC_EU_TRANSFER_CALLS
| >| .
| >| >TC_DNUM
| >| >where CLEC_TXN_PON.PON_VER='217-00'
| >| >
| >| >
| >| >select CLEC_EU_DISCONNECT_INFORMATION.DNUM
| >| >CLEC_EU_DISCONNECT_INFORMATION_DNUM,
| >| >CLEC_TXN_PON.PON_VER
| >|
| >CLEC_TXN_PON_PON_VER,CLEC_EU_DISCONNECT_INFORMATION.TC_OPT
| >|
| >| >from CLEC_TXN_PON left outer join CLEC_EU_LOCATION
| >| >on CLEC_TXN_PON.PON_VER=CLEC_EU_LOCATION.LOC_PON_VER
| >| >left outer join CLEC_EU_DISCONNECT_INFORMATION
| >| >on
| >|
| >CLEC_EU_LOCATION.LOC_PON_VER=CLEC_EU_DISCONNECT_INFORMATIO
| >| N
| >| >..DISC_INFO_PON_VER
| >| >and
| >|
| >CLEC_EU_LOCATION.LOCNUM=CLEC_EU_DISCONNECT_INFORMATION.DIS
| >| C
| >| >_INFO_LOCNUM
| >| >where CLEC_TXN_PON.PON_VER='217-00'
| >| >
| >| >.
| >| >
| >|
| >
| >.
| >
|sql