title: Why do I get these different results from two SQL queries?
body: <p>This has been killing me all day =D.  Please help!</p>
  
  <p>Scenario 1:  Two DB's on the same server (A, B) and A has three tables.  Query is executed from B.</p>
  
  <p>Scenario 2: Two DB's one server is linked to the other. (B has link A) and A has three tables.  Query is executed from B.</p>
  
  <p>In scenario 1 (non linked server):</p>
  
  <pre><code>SET @val = ''
  SELECT @val = @val + 'Hello, my name is ' + [name] + '!' + CHAR(10) + CHAR(13)
  FROM A.sys.tables
  </code></pre>
  
  <p>Returns:
  Hello, my name is Table1!
  Hello, my name is Table2!
  Hello, my name is Table3!</p>
  
  <p>In scenario 2 (linked server):</p>
  
  <pre><code>SET @val = ''
  SELECT @val = @val + 'Hello, my name is ' + [name] + '!' + CHAR(10) + CHAR(13)
  FROM LINKED.A.sys.tables
  </code></pre>
  
  <p>Returns:
  Hello, my name is Table3!</p>
  
  <p>Why are these different?  If I use openquery() on the linked server the results are the same as scenario 1.  I'm trying to avoid using openquery() if possible.  Thanks!</p>