Problem concatenating column values into string...

I have a customer who has recently migrated their SQL server to a new
server.  In doing so, a portion of a stored procedure has stopped
working.  The code snippet is below:

declare @Prefixes varchar(8000),
declare @StationID int
-- ...
select @Prefixes = ''
select @Prefixes = @Prefixes + Prefix + '|||'
from Device
where Station_ID = @StationID

Essentially, we are trying to triple-pipe delimit all the device
prefixes located at a specified station.  This code has worked
flawlessly for the last 10 months, but when the database was restored
on the new server, @Prefixes only contains the prefix for the last
device.

Is there a server, database, or connection option that permits this to
work that I am not aware of?  Why would this work on the old server and
not on the new?  (BTW - both servers are running SQL 2000 Standard
SP4).

Thanks!

The code that you posted looks fine
When you run this

select  Prefix
from Device
where Station_ID = @StationID

how many rows does it return?
Is it possible that the restored DB does not have all the data?


Denis the SQL Menace
http://sqlservercode.blogspot.com/

Sorry, I should have specified more details.  My first thought was that
after restoring the database, a user had changed the station
configuration (unlikely, but possible).  However, the station
configuration is identical on both the new server and that old server.

The station in question, has 2 devices with prefixes 2 and 3.
Therefore, @Prefixes should ressemble '2|||3|||', however I only get
'3|||' on the new server.

When I test your query, it does properly return 2 records with prefixes
2 and 3 (on the new server and on the old server).

Thanks!

Refer this
[url]http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true[/
url]

Madhivanan

david.buckingham@gmail.com (david.buckingham@gmail.com)  writes:
> I have a customer who has recently migrated their SQL server to a new
> server.  In doing so, a portion of a stored procedure has stopped
> working.  The code snippet is below:
>
> declare @Prefixes varchar(8000),
> declare @StationID int
> -- ...
> select @Prefixes = ''
> select @Prefixes = @Prefixes + Prefix + '|||'
> from Device
> where Station_ID = @StationID
>
> Essentially, we are trying to triple-pipe delimit all the device
> prefixes located at a specified station.  This code has worked
> flawlessly for the last 10 months, but when the database was restored
> on the new server, @Prefixes only contains the prefix for the last
> device.
>
> Is there a server, database, or connection option that permits this to
> work that I am not aware of?  Why would this work on the old server and
> not on the new?  (BTW - both servers are running SQL 2000 Standard
> SP4).

Because the result of this operation is undefined. Rewrite the code to run
a cursor instead. On SQL 2005 there is syntax that permits you do this in
one syntax, but it's not the one above. (It's a quite obscure solution
that uses XPath.)

Refer also to http://support.microsoft.com/default.aspx?scid=287515.
Pay particular attention to the the first sentence under CAUSE.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

As a short-term fix, I have replaced this query with a cursor.

The reference to the sqljunkies blog entries doesn't help.  There is no
difference between what I'm accomplishing with the current query,
versus moving that same query into a scalar function.

Erland:  I'm confused regarding the article that you posted.  I am not
calling any functions in the select list, or the order by clause.  I
don't understand "The correct behavior for an aggregate concatenation
query is undefined."  I am successfully using similar queries in
multiple solutions, this is the first time that I have experienced this
problem.  Again, I've only moved the database to a new server and then
this problem began.

Thanks All!
David

david.buckingham@gmail.com (david.buckingham@gmail.com)  writes:
> Erland:  I'm confused regarding the article that you posted.  I am not
> calling any functions in the select list, or the order by clause.  I
> don't understand "The correct behavior for an aggregate concatenation
> query is undefined."

An aggregate concatenation is when you try:

SELECT @x = @x + col FROM tbl

that is, precisly what you had in your code.

And the result of this operation is undefined. That is, there is no
guarantee that you get the result you expect.

The article itself is admittedly strange, because if first says
that this type of operation is undefined, and then it tries to explain
how you should use it anyway.

> I am successfully using similar queries in multiple solutions,

Yes, the method is deceivable, as it often gives the desired result,
despite that there is no guarantee for it.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx