Can anyone explain this? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Can anyone explain this?

Author

Topic

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2002-01-24 : 11:00:08

The following program is a simple program designed to build a small comma-delimited list of EmployeeIDs. Run the program as is and you should get a list similar to "7,2,8".


USE Northwind
DECLARE @v1 VARCHAR(100)
SELECT @v1 = ''

SELECT @v1 = CAST(O.EmployeeID AS VARCHAR(1)) + ',' + @v1
FROM Orders AS O
WHERE O.CustomerID = 'CONSH'
--ORDER BY O.EmployeeID

SELECT @v1

Now, uncomment the ORDER BY and you should only get one record, the last one. Why? The other interesting thing is that the Messages says "(1 row(s) affected)" even when it worked and returned all 3. What am I missing?

I am using SQL Server 2000 unpatched on Windows 2000.

Thanks.

izaltsman
A custom title

1139 Posts

Posted - 2002-01-24 : 11:58:08

When I uncomment ORDER BY I still get a string of all three comma-separated values ("8,7,2,"), so I am not quite sure what you are talking about.
"1 row(s) affected" is the correct message for this situation: the last command in the batch returns a single row (the CSV list) and that is what the message is trying to tell you.

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2002-01-24 : 12:45:04

quote:

When I uncomment ORDER BY I still get a string of all three comma-separated values ("8,7,2,"), so I am not quite sure what you are talking about.
"1 row(s) affected" is the correct message for this situation: the last command in the batch returns a single row (the CSV list) and that is what the message is trying to tell you.

I am definitely getting only one value back: "8,". I have tested this with numerous tables and data and it is repeatable.

Are you running a patched version of SQL Server? If so, which patch?

Thanks.

Jerry

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-01-24 : 13:08:37

I have SQL Server 2000 SP2. Version information is :

----------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: )

It worked fine for me both with and without the ORDER BY statement.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

chadmat
The Chadinator

1974 Posts

Posted - 2002-01-24 : 13:13:08

I am on SP1, I get all three.

-Chad

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2002-01-24 : 13:14:42

quote:

I am on SP1, I get all three.
-Chad

We just put SP2 on a machine and the code worked as expected. Obviously a bug. Thanks guys.

Jerry

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-01-24 : 23:08:03

i have a similar problem but i have a table

table: owner_talk_group
owner_id varchar(10)
device_id varchar(20)
talkgroup varchar(3)

owner and device can be duplicate i.e same device can have more than one talk group.... then how do i get in a coloum

owner device talkgroup
ex: 0221234 1212354654 2,5,6

======================================
Ask to your self before u ask someone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources