Sql Server string concat with order by and subquery
String concatenation in that form combined with ORDER BY is undefined (and unstable and unreliable). I have seen the same symptom where you only get one result. If you want all the rows, drop the ORDER BY (ordering won't be guaranteed, but maybe that's ok, since your TOP 100 doesn't have an ORDER BY, so that's not guaranteed to be deterministic either). If you need ORDER BY, use XML PATH or STRING_AGG() instead, depending on version.

SELECT @sql = STUFF((
  SELECT ',' + t.error + CONVERT(varchar(12), xC.[object_id]) + ',' 
    + t.error + ',' + t.error + ',' + t.error + ',' 
    + t.error2 + ',' -- carriage returns are good for the soul!
    + t.error + ',' + t.error + ',' + t.error + ',' 
FROM #test AS t
INNER JOIN sys.columns AS xC 
  ON t.object_id = xC.object_id
ORDER BY t.tmp_id -- this is the key to ordering
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'');


On 2017+ you can use:

SELECT @sql = STRING_AGG(t.error + CONVERT(varchar(11), xC.[object_id]) + ',' 
    + t.error + ',' + t.error + ',' + t.error + ',' 
    + t.error2 + ',' 
    + t.error + ',' + t.error + ',' + t.error, ',') 
  WITHIN GROUP (ORDER BY t.tmp_id)
FROM #test AS t
INNER JOIN sys.columns AS xC 
  ON t.object_id = xC.object_id;


For more info, see Order by 1 shortens the result set to one row and the links therein.