SQL multiple assignment variable only has 1 (one) row

Question

In SQL, if you have a query like this:

SELECT @text = @text + FirstName + ', '
  FROM Persons

This normally produces a @text variable of 'John, Jack, Pete, ' etc.

However, in my case this query:

SELECT @text = @text + FirstName + ', '
  FROM Server.Database.dbo.Persons

Only returned one row. The database is SQL 2008 R2.

Answer 1

Apparently this technique only works on local databases. Linked server queries send the variable back and forth only once, hence just the last row is appended.

The solution is to store the results in a local temp table and then do the MAV:

SELECT FirstName + ', ' AS t
  INTO #temp
  FROM Server.Database.dbo.Persons

SELECT @text = @text + t FROM #temp;