Incorrect week number with DATENAME in localized query

Hi,

I'm trying to use the DATENAME function to get a correct Dutch week number, but the DATENAME function seems not to return a localized week number. This is how I have tested it:

-- Set language to English
SET LANGUAGE us_English

-- Declare to dates 12/30/2006 and 12/31/2006
DECLARE @.Dec30 AS DATETIME SET @.Dec30 = CONVERT(DateTime, '2006-12-30')
DECLARE @.Dec31 AS DATETIME SET @.Dec31 = CONVERT(DateTime, '2006-12-31')

-- Return information about the declared dates in English
SELECT @.Dec30 as date1, DATENAME(week, @.Dec30) as week1, DATENAME(weekday, @.Dec30) as day1,
@.Dec31 as date2, DATENAME(week, @.Dec31) as week2, DATENAME(weekday, @.Dec31) as day2

-- Set language to Dutch
SET LANGUAGE Dutch

-- Return information about the declared dates in Dutch
SELECT @.Dec30 as date1, DATENAME(week, @.Dec30) as week1, DATENAME(weekday, @.Dec30) as day1,
@.Dec31 as date2, DATENAME(week, @.Dec31) as week2, DATENAME(weekday, @.Dec31) as day2

In both the English and Dutch results Saturday (12/30/2006) has week number 52 and Sunday (12/31/2006) has week number 53, but this is incorrect for the Dutch language. Sunday should also have week number 52, because the week starts on Monday in The Netherlands.

What am I doing wrong here and how can I get the correct localized week numbers from SQL Server? (I'm using SQL Server 2000 + SP4)

Thanks in advance.

SET Language is used configure the following options, DateFormat, DateFirst, Names of the Month & Names of the Days. So your in the rite direction to get your result..

BUT,

Unfortuantlly SQL Server won't help you to get the proper Week Number. Bcs they are not following ISO standard as you think. The Week 1 always = 1 - jan -any year. So sometimes you will get wrong week number.

The best approach to get the week number is use the custom function to get the week number.

Create Function dbo.MyWeekNo(@.dateFirst int, @.DateValue as DateTime) Returns Int
As
Begin
Declare @.Date as Datetime
declare @.Date2 as Datetime
Declare @.Week as int
Select @.Date = Convert(Varchar,Year(@.DateValue)) + '-01-01', @.Date2=DateAdd(DD,-1,@.Date)

Select @.Week = Case When WeekNo=0 Then dbo.MyWeekNo(@.dateFirst,@.Date2) Else WeekNo End
From
(
Select
Case When DatePart(W,@.Date) >= @.dateFirst Then DatePart(WW,@.DateValue) -1
Else DatePart(WW,@.DateValue) End WeekNo
) as Weeks

Return @.Week;
End

|||

Hi ManiD,

Your function is very close to the function I'm using for years now:

CREATE FUNCTION dbo.DutchWeek(@.DATE AS DateTime) RETURNS Int AS
BEGIN
IF @.DATE IS NULL RETURN NULL;

DECLARE @.JANFIRST AS DateTime
DECLARE @.WEEKDAY AS Int
DECLARE @.DAY AS Int
DECLARE @.DAYOFYEAR AS Int
DECLARE @.WEEKNUMBER AS Int

-- Get Januari the first of the year of @.DATE
SET @.JANFIRST = CONVERT(datetime, '1/1/' + CONVERT(varchar, YEAR(@.DATE)))

-- Calculate the number of the day where 0 = Monday, 1 = Tuesday, etc...
SET @.WEEKDAY = CONVERT(Int, @.JANFIRST) % 7

-- Calculate the (zero-bases) day number (0..265)
SET @.DAYOFYEAR = CONVERT(integer, @.DATE - @.JANFIRST)
-- Calculate the dutch week number
SET @.WEEKNUMBER = (@.DAYOFYEAR + @.WEEKDAY) / 7 +
CASE WHEN @.WEEKDAY > 3 THEN 0 ELSE 1 END

-- When week number is 0, get the weeknumber of the last week of the
-- previous year
IF @.WEEKNUMBER = 0
SET @.WEEKNUMBER = dbo.DutchWeek('12/31/' +
CONVERT(varchar, YEAR(@.DATE)-1));
RETURN @.WEEKNUMBER;
END

I use this function for years, but always had the feeling SQL Server should do this for me. But this is not the case, is it?

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

Incorrect Order in rendering report

Hi,

I have this problem on Reporting Services 2005 SP2:

There is a stored procedure that is the source of a dataset in report, this procedure return a recordset ordered by some fileds (es. order by fields1, fields2, ecc...). This procedure also have some parameters, but this isn't important.

If I launch the stored procedure in sql server management studio the data are returned in the correct order, instead, when I run the report, the data are showed in wrong order.

Some one have informations about this issue?

Kind Regards,

Elia.

Did you try sorting in the report? If so doesn't it still sort in the order that you selected.

If not try sorting in the report, within table properties you will see sorting within which you can specify the sort order

|||

Thanks,

I have resolved the problem.

Regards,

Elia.

Incorrect behavior in 'NOT IN' subquery with OPENXML

In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a subquery.
However, this does not always return the desired result.
I've tried to simplify this problem and I've ended up with the sql script
below. In short, it creates a table with 200 records, select all records
except two specified in an xml document. When I run the complete script (in
the Query Analyzer) I get the desired results: 198 records. But when I first
run the part of the script where the table is created and filled (up to the
line of dashes), end then run the rest of the script where the xml is
prepared and the query is executed I get another result: 200 records.
I tried this script on two different Sql Server 2000 SP4 machines, both have
the same behavior. But this behavior seems incorrect to me.
So my questions:
- Does anybody get the correct result when you execute this script in parts?
- I this behavior indeed indeed incorrect or am I not thinking straight?
- Or could this be a bug in SQL Server...?
Thanks for your help!
Some remarks:
- When the table contains 160 records or less I get the correct result.
- When I use an 'IN' subquery (so without the NOT) I always get the correct
result (so the two specified records are returned both in the 'IN' as in the
'NOT IN' subquery...).
- When I run the subquery separately it returns the expected result: two
rows with values 1 and 2.
SET NOCOUNT ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Test]
CREATE TABLE [dbo].[Test] (
TestId int NOT NULL PRIMARY KEY
)
DECLARE @.Value int
SET @.Value = 1
WHILE @.Value <= 200
BEGIN
INSERT INTO Test (TestId) VALUES (@.Value)
SET @.Value = @.Value + 1
END

DECLARE @.Xml nvarchar(4000)
DECLARE @.XmlHandle int
SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
SELECT TestId
FROM Test
WHERE TestId NOT IN (
SELECT TestId
FROM OPENXML(@.XmlHandle, '/Root/Row')
WITH (
TestIdint'ID'
)
)
ORDER BY TestId
EXEC sp_xml_removedocument @.XmlHandle
DROP TABLE [dbo].[Test]
SET NOCOUNT OFF
I have tried both in SQL Server 2005 and got the same result (198 rows). I
don't have an SP4 installation on my machine, but asked our test team to
investigate. It may take a couple of days though until we can get back to
you...
Best regards
Michael
"Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
> In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a
> subquery.
> However, this does not always return the desired result.
> I've tried to simplify this problem and I've ended up with the sql script
> below. In short, it creates a table with 200 records, select all records
> except two specified in an xml document. When I run the complete script
> (in
> the Query Analyzer) I get the desired results: 198 records. But when I
> first
> run the part of the script where the table is created and filled (up to
> the
> line of dashes), end then run the rest of the script where the xml is
> prepared and the query is executed I get another result: 200 records.
> I tried this script on two different Sql Server 2000 SP4 machines, both
> have
> the same behavior. But this behavior seems incorrect to me.
> So my questions:
> - Does anybody get the correct result when you execute this script in
> parts?
> - I this behavior indeed indeed incorrect or am I not thinking straight?
> - Or could this be a bug in SQL Server...?
> Thanks for your help!
> Some remarks:
> - When the table contains 160 records or less I get the correct result.
> - When I use an 'IN' subquery (so without the NOT) I always get the
> correct
> result (so the two specified records are returned both in the 'IN' as in
> the
> 'NOT IN' subquery...).
> - When I run the subquery separately it returns the expected result: two
> rows with values 1 and 2.
>
> SET NOCOUNT ON
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
> OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
> DROP TABLE [dbo].[Test]
> CREATE TABLE [dbo].[Test] (
> TestId int NOT NULL PRIMARY KEY
> )
> DECLARE @.Value int
> SET @.Value = 1
> WHILE @.Value <= 200
> BEGIN
> INSERT INTO Test (TestId) VALUES (@.Value)
> SET @.Value = @.Value + 1
> END
>
> ----
>
> DECLARE @.Xml nvarchar(4000)
> DECLARE @.XmlHandle int
> SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
> EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
> SELECT TestId
> FROM Test
> WHERE TestId NOT IN (
> SELECT TestId
> FROM OPENXML(@.XmlHandle, '/Root/Row')
> WITH (
> TestId int 'ID'
> )
> )
> ORDER BY TestId
> EXEC sp_xml_removedocument @.XmlHandle
> DROP TABLE [dbo].[Test]
> SET NOCOUNT OFF
>
>
|||We checked it against SQL 2000 SP3a and SP4. First the good news: SP4 seems
to work fine in either case.
When running it under SP3a: we observed:
1) run whole script - gets 198 rows (only one Remote Scan involved)
2) run create/fill 'Test' table first, and then OpenXML part, get 200 rows
(wrong behavior, two Remote Scans involved).
We seem to be able to correct this by setting "set ansi_nulls off".
Can you please check with select @.@.version what version number you are
running? And if you see version 8.00.2039 can you please tell us how you
installed it?
Thanks
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u6dGBzyZFHA.3568@.TK2MSFTNGP10.phx.gbl...
>I have tried both in SQL Server 2005 and got the same result (198 rows). I
>don't have an SP4 installation on my machine, but asked our test team to
>investigate. It may take a couple of days though until we can get back to
>you...
> Best regards
> Michael
> "Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
> news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
>
|||Before posting I checked @.@.VERSION and since it mentioned SP4 I figured I had
SQL Server 2000 SP4. Only now did I read it a bit better to see we're running
2000 SP3:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
(Sorry, I got fooled by the "Service Pack 4" at the end... :-$ )
The "set ansi_nulls off" indeed seems to correct this issue.
I did find another way of circumventing this behavior: inserting the values
from the xml into a temporary table and using that temporary table in the
subquery.
In any case: I understand that this behavior is corrected in the next SP, so
that's good. And of course thank you for your time and help with this matter.
Regards,
Wouter

Incorrect behavior in 'NOT IN' subquery with OPENXML

In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a subquery.
However, this does not always return the desired result.
I've tried to simplify this problem and I've ended up with the sql script
below. In short, it creates a table with 200 records, select all records
except two specified in an xml document. When I run the complete script (in
the Query Analyzer) I get the desired results: 198 records. But when I first
run the part of the script where the table is created and filled (up to the
line of dashes), end then run the rest of the script where the xml is
prepared and the query is executed I get another result: 200 records.
I tried this script on two different Sql Server 2000 SP4 machines, both have
the same behavior. But this behavior seems incorrect to me.
So my questions:
- Does anybody get the correct result when you execute this script in parts?
- I this behavior indeed indeed incorrect or am I not thinking straight?
- Or could this be a bug in SQL Server...?
Thanks for your help!
Some remarks:
- When the table contains 160 records or less I get the correct result.
- When I use an 'IN' subquery (so without the NOT) I always get the correct
result (so the two specified records are returned both in the 'IN' as in the
'NOT IN' subquery...).
- When I run the subquery separately it returns the expected result: two
rows with values 1 and 2.
SET NOCOUNT ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Test]
CREATE TABLE [dbo].[Test] (
TestId int NOT NULL PRIMARY KEY
)
DECLARE @.Value int
SET @.Value = 1
WHILE @.Value <= 200
BEGIN
INSERT INTO Test (TestId) VALUES (@.Value)
SET @.Value = @.Value + 1
END
----
DECLARE @.Xml nvarchar(4000)
DECLARE @.XmlHandle int
SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
SELECT TestId
FROM Test
WHERE TestId NOT IN (
SELECT TestId
FROM OPENXML(@.XmlHandle, '/Root/Row')
WITH (
TestId int 'ID'
)
)
ORDER BY TestId
EXEC sp_xml_removedocument @.XmlHandle
DROP TABLE [dbo].[Test]
SET NOCOUNT OFFI have tried both in SQL Server 2005 and got the same result (198 rows). I
don't have an SP4 installation on my machine, but asked our test team to
investigate. It may take a couple of days though until we can get back to
you...
Best regards
Michael
"Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
> In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a
> subquery.
> However, this does not always return the desired result.
> I've tried to simplify this problem and I've ended up with the sql script
> below. In short, it creates a table with 200 records, select all records
> except two specified in an xml document. When I run the complete script
> (in
> the Query Analyzer) I get the desired results: 198 records. But when I
> first
> run the part of the script where the table is created and filled (up to
> the
> line of dashes), end then run the rest of the script where the xml is
> prepared and the query is executed I get another result: 200 records.
> I tried this script on two different Sql Server 2000 SP4 machines, both
> have
> the same behavior. But this behavior seems incorrect to me.
> So my questions:
> - Does anybody get the correct result when you execute this script in
> parts?
> - I this behavior indeed indeed incorrect or am I not thinking straight?
> - Or could this be a bug in SQL Server...?
> Thanks for your help!
> Some remarks:
> - When the table contains 160 records or less I get the correct result.
> - When I use an 'IN' subquery (so without the NOT) I always get the
> correct
> result (so the two specified records are returned both in the 'IN' as in
> the
> 'NOT IN' subquery...).
> - When I run the subquery separately it returns the expected result: two
> rows with values 1 and 2.
>
> SET NOCOUNT ON
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
> OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
> DROP TABLE [dbo].[Test]
> CREATE TABLE [dbo].[Test] (
> TestId int NOT NULL PRIMARY KEY
> )
> DECLARE @.Value int
> SET @.Value = 1
> WHILE @.Value <= 200
> BEGIN
> INSERT INTO Test (TestId) VALUES (@.Value)
> SET @.Value = @.Value + 1
> END
>
> ----
>
> DECLARE @.Xml nvarchar(4000)
> DECLARE @.XmlHandle int
> SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
> EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
> SELECT TestId
> FROM Test
> WHERE TestId NOT IN (
> SELECT TestId
> FROM OPENXML(@.XmlHandle, '/Root/Row')
> WITH (
> TestId int 'ID'
> )
> )
> ORDER BY TestId
> EXEC sp_xml_removedocument @.XmlHandle
> DROP TABLE [dbo].[Test]
> SET NOCOUNT OFF
>
>|||We checked it against SQL 2000 SP3a and SP4. First the good news: SP4 seems
to work fine in either case.
When running it under SP3a: we observed:
1) run whole script - gets 198 rows (only one Remote Scan involved)
2) run create/fill 'Test' table first, and then OpenXML part, get 200 rows
(wrong behavior, two Remote Scans involved).
We seem to be able to correct this by setting "set ansi_nulls off".
Can you please check with select @.@.version what version number you are
running? And if you see version 8.00.2039 can you please tell us how you
installed it?
Thanks
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u6dGBzyZFHA.3568@.TK2MSFTNGP10.phx.gbl...
>I have tried both in SQL Server 2005 and got the same result (198 rows). I
>don't have an SP4 installation on my machine, but asked our test team to
>investigate. It may take a couple of days though until we can get back to
>you...
> Best regards
> Michael
> "Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
> news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
>|||Before posting I checked @.@.VERSION and since it mentioned SP4 I figured I ha
d
SQL Server 2000 SP4. Only now did I read it a bit better to see we're runnin
g
2000 SP3:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
(Sorry, I got fooled by the "Service Pack 4" at the end... :-$ )
The "set ansi_nulls off" indeed seems to correct this issue.
I did find another way of circumventing this behavior: inserting the values
from the xml into a temporary table and using that temporary table in the
subquery.
In any case: I understand that this behavior is corrected in the next SP, so
that's good. And of course thank you for your time and help with this matter
.
Regards,
Wouter

Inconsistent behaviour of SQL

Folks, We have the following sql statement which we run against two
databases of same structure
But return different returns. Any help is appreciated.
Select @.@.version
go
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
Calist Site
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE =
'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM =
016598
go
The above works fine
Dub1 Site
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE =
'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM =
004811
go
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
Go
The red statement gets...
Fatal Error: Syntax error converting the varchar value '.W
_____
.. ' to a column of data type int.
The Table structure is as under (same for both the databases)
CREATE TABLE [dbo].[PLAYER] (
[INDIVIDUAL_ID] [int] NOT NULL ,
[PLAY_JOIN_DATE] [datetime] NULL ,
[PLAY_MBRSHIP_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_FINANCIAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_LEVEL] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PLAY_BADGE_NUM] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[PLAY_OCCUPATION] [char] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_MARITAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_INCOME_LEVEL] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NUMBER] [int] NOT NULL ,
[PLAY_INPLAY_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_CREATE_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_USERID] [char] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] WITH NOCHECK ADD
CONSTRAINT [PK_PLAYER] PRIMARY KEY CLUSTERED
(
[INDIVIDUAL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] ADD
CONSTRAINT [FK_PLAYER_ACCOUNT] FOREIGN KEY
(
[ACCT_NUMBER]
) REFERENCES [dbo].[ACCOUNT] (
[ACCT_NUMBER]
),
CONSTRAINT [FK_PLAYER_INDIVIDUAL] FOREIGN KEY
(
[INDIVIDUAL_ID]
) REFERENCES [dbo].[INDIVIDUAL] (
[INDIVIDUAL_ID]
)
GO
Your problem (which you described with so much detail) can be
summarized by the following repro script:
CREATE TABLE Test (A varchar(5) PRIMARY KEY)
INSERT INTO Test VALUES ('123')
SELECT * FROM Test WHERE A=1
-- runs fine
INSERT INTO Test VALUES ('XYZ')
SELECT * FROM Test WHERE A=1
-- Syntax error converting the varchar value 'XYZ' to a column of data
type int.
This is expected behaviour, because when comparing a varchar column to
an int constant, SQL Server tries to convert the varchar to an int,
according to the data type precedence rules.
For more informations, see:
http://msdn.microsoft.com/library/en...da-db_2js5.asp
The solution is, of course, to specify a varchar constant (this way SQL
Server would not need to do any conversions, so it can also use an
index, if one exists).
Razvan
|||THX for the summary Razvan.
My question was:
SQL server does the conversion in all the databases my databases with the
same structure on the same sql server except this particular one.
Wondering if anyone has come across the situation where the SQL implicitly
converts (as per
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp)
Is this a particular patch thing, etc.
Regards,
Subhash
"Razvan Socol" wrote:

> Your problem (which you described with so much detail) can be
> summarized by the following repro script:
> CREATE TABLE Test (A varchar(5) PRIMARY KEY)
> INSERT INTO Test VALUES ('123')
> SELECT * FROM Test WHERE A=1
> -- runs fine
> INSERT INTO Test VALUES ('XYZ')
> SELECT * FROM Test WHERE A=1
> -- Syntax error converting the varchar value 'XYZ' to a column of data
> type int.
>
> This is expected behaviour, because when comparing a varchar column to
> an int constant, SQL Server tries to convert the varchar to an int,
> according to the data type precedence rules.
> For more informations, see:
> http://msdn.microsoft.com/library/en...da-db_2js5.asp
> The solution is, of course, to specify a varchar constant (this way SQL
> Server would not need to do any conversions, so it can also use an
> index, if one exists).
> Razvan
>
|||> SQL server does the conversion in all the databases my databases with the
> same structure on the same sql server except this particular one.
Probably, this is the only database that has some non-numeric values in
that char(6) column.
Razvan

Inconsistent behaviour of SQL

Folks, We have the following sql statement which we run against two
databases of same structure
But return different returns. Any help is appreciated.
Select @.@.version
go
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
Calist Site
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE =
'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM =
016598
go
The above works fine
Dub1 Site
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE =
'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM =
004811
go
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
Go
The red statement gets...
Fatal Error: Syntax error converting the varchar value '.W
_____
. ' to a column of data type int.
The Table structure is as under (same for both the databases)
CREATE TABLE [dbo].[PLAYER] (
[INDIVIDUAL_ID] [int] NOT NULL ,
[PLAY_JOIN_DATE] [datetime] NULL ,
[PLAY_MBRSHIP_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_FINANCIAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_LEVEL] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PLAY_BADGE_NUM] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[PLAY_OCCUPATION] [char] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_MARITAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_INCOME_LEVEL] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NUMBER] [int] NOT NULL ,
[PLAY_INPLAY_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_CREATE_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_USERID] [char] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] WITH NOCHECK ADD
CONSTRAINT [PK_PLAYER] PRIMARY KEY CLUSTERED
(
[INDIVIDUAL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] ADD
CONSTRAINT [FK_PLAYER_ACCOUNT] FOREIGN KEY
(
[ACCT_NUMBER]
) REFERENCES [dbo].[ACCOUNT] (
[ACCT_NUMBER]
),
CONSTRAINT [FK_PLAYER_INDIVIDUAL] FOREIGN KEY
(
[INDIVIDUAL_ID]
) REFERENCES [dbo].[INDIVIDUAL] (
[INDIVIDUAL_ID]
)
GOYour problem (which you described with so much detail) can be
summarized by the following repro script:
CREATE TABLE Test (A varchar(5) PRIMARY KEY)
INSERT INTO Test VALUES ('123')
SELECT * FROM Test WHERE A=1
-- runs fine
INSERT INTO Test VALUES ('XYZ')
SELECT * FROM Test WHERE A=1
-- Syntax error converting the varchar value 'XYZ' to a column of data
type int.
This is expected behaviour, because when comparing a varchar column to
an int constant, SQL Server tries to convert the varchar to an int,
according to the data type precedence rules.
For more informations, see:
http://msdn.microsoft.com/library/e..._da-db_2js5.asp
The solution is, of course, to specify a varchar constant (this way SQL
Server would not need to do any conversions, so it can also use an
index, if one exists).
Razvan|||THX for the summary Razvan.
My question was:
SQL server does the conversion in all the databases my databases with the
same structure on the same sql server except this particular one.
Wondering if anyone has come across the situation where the SQL implicitly
converts (as per
http://msdn.microsoft.com/library/d...br />
2f3o.asp)
Is this a particular patch thing, etc.
Regards,
Subhash
"Razvan Socol" wrote:

> Your problem (which you described with so much detail) can be
> summarized by the following repro script:
> CREATE TABLE Test (A varchar(5) PRIMARY KEY)
> INSERT INTO Test VALUES ('123')
> SELECT * FROM Test WHERE A=1
> -- runs fine
> INSERT INTO Test VALUES ('XYZ')
> SELECT * FROM Test WHERE A=1
> -- Syntax error converting the varchar value 'XYZ' to a column of data
> type int.
>
> This is expected behaviour, because when comparing a varchar column to
> an int constant, SQL Server tries to convert the varchar to an int,
> according to the data type precedence rules.
> For more informations, see:
> http://msdn.microsoft.com/library/e..._da-db_2js5.asp
> The solution is, of course, to specify a varchar constant (this way SQL
> Server would not need to do any conversions, so it can also use an
> index, if one exists).
> Razvan
>|||> SQL server does the conversion in all the databases my databases with the
> same structure on the same sql server except this particular one.
Probably, this is the only database that has some non-numeric values in
that char(6) column.
Razvan

Inconsistent behaviour of SQL

Folks, We have the following sql statement which we run against two
databases of same structure
But return different returns. Any help is appreciated.
Select @.@.version
go
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
--
Calist Site
--
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE = 'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM = 016598
go
The above works fine
Dub1 Site
SELECT CODE_VALUE FROM CODES WHERE COTB_NAME = 'TITLE' AND CODE_VALUE = 'MISS'
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION
go
SELECT PLAY.INDIVIDUAL_ID FROM PLAYER PLAY WHERE PLAY.PLAY_BADGE_NUM = 004811
go
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
Go
The red statement gets...
Fatal Error: Syntax error converting the varchar value '.W
_____
. ' to a column of data type int.
The Table structure is as under (same for both the databases)
CREATE TABLE [dbo].[PLAYER] (
[INDIVIDUAL_ID] [int] NOT NULL ,
[PLAY_JOIN_DATE] [datetime] NULL ,
[PLAY_MBRSHIP_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_FINANCIAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_LEVEL] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PLAY_BADGE_NUM] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[PLAY_OCCUPATION] [char] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_MARITAL_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_INCOME_LEVEL] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NUMBER] [int] NOT NULL ,
[PLAY_INPLAY_STS] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAY_CREATE_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_DATE] [datetime] NOT NULL ,
[PLAY_MODIFY_USERID] [char] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] WITH NOCHECK ADD
CONSTRAINT [PK_PLAYER] PRIMARY KEY CLUSTERED
(
[INDIVIDUAL_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PLAYER] ADD
CONSTRAINT [FK_PLAYER_ACCOUNT] FOREIGN KEY
(
[ACCT_NUMBER]
) REFERENCES [dbo].[ACCOUNT] (
[ACCT_NUMBER]
),
CONSTRAINT [FK_PLAYER_INDIVIDUAL] FOREIGN KEY
(
[INDIVIDUAL_ID]
) REFERENCES [dbo].[INDIVIDUAL] (
[INDIVIDUAL_ID]
)
GOYour problem (which you described with so much detail) can be
summarized by the following repro script:
CREATE TABLE Test (A varchar(5) PRIMARY KEY)
INSERT INTO Test VALUES ('123')
SELECT * FROM Test WHERE A=1
-- runs fine
INSERT INTO Test VALUES ('XYZ')
SELECT * FROM Test WHERE A=1
-- Syntax error converting the varchar value 'XYZ' to a column of data
type int.
This is expected behaviour, because when comparing a varchar column to
an int constant, SQL Server tries to convert the varchar to an int,
according to the data type precedence rules.
For more informations, see:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp
The solution is, of course, to specify a varchar constant (this way SQL
Server would not need to do any conversions, so it can also use an
index, if one exists).
Razvan|||THX for the summary Razvan.
My question was:
SQL server does the conversion in all the databases my databases with the
same structure on the same sql server except this particular one.
Wondering if anyone has come across the situation where the SQL implicitly
converts (as per
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp)
Is this a particular patch thing, etc.
Regards,
Subhash
"Razvan Socol" wrote:
> Your problem (which you described with so much detail) can be
> summarized by the following repro script:
> CREATE TABLE Test (A varchar(5) PRIMARY KEY)
> INSERT INTO Test VALUES ('123')
> SELECT * FROM Test WHERE A=1
> -- runs fine
> INSERT INTO Test VALUES ('XYZ')
> SELECT * FROM Test WHERE A=1
> -- Syntax error converting the varchar value 'XYZ' to a column of data
> type int.
>
> This is expected behaviour, because when comparing a varchar column to
> an int constant, SQL Server tries to convert the varchar to an int,
> according to the data type precedence rules.
> For more informations, see:
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp
> The solution is, of course, to specify a varchar constant (this way SQL
> Server would not need to do any conversions, so it can also use an
> index, if one exists).
> Razvan
>|||> SQL server does the conversion in all the databases my databases with the
> same structure on the same sql server except this particular one.
Probably, this is the only database that has some non-numeric values in
that char(6) column.
Razvan

Include ID field in GROUP BY statement

I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.

The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':

ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83

And run the query:

select max(Cost),FruitName From Fruits
group by FruitName

It'll correctly return:

FruitName Cost
Apple 0.63
Pear 0.89

Now i need the ID also returned by my query so i go:

select max(Cost),FruitName,ID From Fruits
group by FruitName,ID

This doesnt return the above results with the ID appended to it, it instead returns:

ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83

As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:

ID FruitName Cost
2 Apple 0.63
4 Pear 0.89

Thanks.

I don't think you can do that with the ID field because there are multiple Ids for each item while using the group by. Can you use a Having clause? ie

select Cost,FruitName From Fruits
group by FruitName
having max(Cost)

This is a shot in the dark as i did not actually try your data. Hope it works though :)

|||

Thanks but dont think that would work as the 'HAVING' as i see it is just used to filter the results of the query so you'd use it if for example you wanted to only show max fuit more than ￡0.10

select Cost,FruitName From Fruit
group by FruitName,Cost
having max(Cost) > 0.1

Thats how i see it (could be wrong). But it still doesnt handle the problem of the ID.

|||

Hi,

This is a round about way, but it works

Select

FruitName,cost,(Select IDfrom Fruits fWhere f.Cost=frt.cost)as IDfrom

(

select FruitName,max(cost)as costFrom Fruits

group

by FruitName) frt|||

Hi TheGrox,

The below query may help you.

select id,fruitname,costfrom (select row_number()over (partitionby fruitnameorder by costdesc)as rownum,id,fruitname,costfrom Fruits) Fwhere rownum = 1

The main trick here is using the ROW_NUMBER() OVER (PARTITION BY...) clause

This is very like getting top n records from a table belonging to each category. You can follow the article athttp://www.kodyaz.com/articles/top-n-random-rows-foreach-category-or-group.aspx for more details and a sample for ROW_NUMBER() OVER (PARTITION BY...)

Eralper

http://www.kodyaz.com

|||

If you are using SQL 2000 you can try this:

Select t1.FruitName,t2.cost, Idfrom Fruits t1join (select FruitName,max(cost)as costFrom Fruitsgroup by FruitName ) t2on t1.FruitName = t2.fruitnameand t1.Cost = t2.cost

|||

Here is another one:

select t1.*from fruitsas t1where t1.ID= (select top 1 t2.IDfrom fruitsas t2where t2.FruitName = t1.FruitNameorder by t2.costDESC)

|||

Limno

I like your query better than mine. I am joining on name and cost. If there are multiple records with same name and costs, the join could return multiple records for same "Fruit" which might be incorrect.

|||I learned that solution from Umachandar Jayachandran. He is super.