Concatenation into a scalar variable using linked server in FROM clause fails when casting or converting data - by abair34

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
1
Sign in
to vote
ID 606786 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 9/29/2010 1:10:58 PM
Access Restriction Public

Description

IN SQL Server you can take the data of a row and fill a scalar variable with the data by running a query such as the following one.  The result is correct (a fully concatenated string of data) when run locally even if you cast or convert the datatype.  But when run against a linked server specified in the FROM clause only the last record is shown.

Example:
DECLARE @concatenated_string as nvarchar(max);
SET @concatenated_string = '';

SELECT @concatenated_string = @concatenated_string 
        + CAST(SOMECHARCOLUMN as varchar(max)) + ', ' 
FROM dbo.SOMETABLE

SELECT @concatenated_string;

The above statement does not work over a linked server to another sql server if the linkedserver is specified in the FROM clause with the [server].[database].[schema].[table] notation.  

Also oddly enough other scalar system functions applied to the column do work such as LEFT(SOMECOL, 1).  

Another oddity is that if you use the
EXEC(N'query...') at [linkedservername] syntax then the results return correctly.

--------------------------------------
Tested in:
Microsoft SQL Server 2005 - 9.00.4266.00 (X64)   Oct  7 2009 17:38:17   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) 
AND
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 

with a linked server defined with the following options
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'sql04,55104', @optname=N'use remote collation', @optvalue=N'true'

Sign in to post a comment.
Posted by Microsoft on 9/30/2010 at 4:39 PM
Hi,
Even without ORDER BY, we do not guarantee that @var = @var + <expression> will produce the concatenated value for any statement that affects multiple rows. The right-hand side of the expression can be evaluated either once or multiple times during query execution and the behavior as I said is plan dependent. So you should modify your query to use one of the methods that I suggested to get the expected results.

Thanks
Umachandar
Posted by abair34 on 9/30/2010 at 11:33 AM
Hi there, Thanks for the quick response. This is actually a different issue. The ORDER BY is not necessary to reproduce the issue...

DECLARE @dblist varchar(max)
SET @dblist = ' '

SELECT @dblist = @dblist + cast(database_id as varchar(10)) + ' '
FROM [SQL04,55104].master.sys.databases
SELECT @dblist

Give that a shot and you'll see that the results are the same.

Does that KB only apply to SQL 2000 and prior?
Posted by Microsoft on 9/30/2010 at 11:17 AM
Hi,
Thanks for your feedback. The behavior you are seeing is by design. Using assignment operations (concatenation in this example) in queries with ORDER BY clause has undefined behavior. This can change from release to release or even within a particular server version due to changes in the query plan. You cannot rely on this behavior even if it works for some queries. See the below KB article for more details:

http://support.microsoft.com/kb/287515

The ONLY guaranteed mechanism to do the value concatenation are the following:

1. Use cursor to loop through the rows in specific order and concatenate the values
2. Use for xml query with ORDER BY to generate the concatenated values
3. Use CLR aggregate (this will not work with ORDER BY clause)

--
Umachandar, SQL Programmability Team