Posted by Erland Sommarskog on 04/06/06 01:02

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]