title: Querying different collations across linked servers - works one direction, not the other
body: <p>I'm trying to query between 2 servers with different collations. One query works, the other doesn't. I want to know why.</p>
  <p>ServerA is 2019 RTM using Latin1_General_BIN collation.<br />
  ServerB is 2016 SP1 using SQL_Latin1_General_CP1_CI_AS collation.</p>
  <p>The linked server properties for both servers (the link to ServerB from ServerA and vice versa) are identical, including the '<strong>Use Remote Collation</strong>' setting, which is set to False. '<strong>Collation Compatible</strong>' is False, and '<strong>Collation Name</strong>' is blank.</p>
  <p>When I run this query from ServerA I get the error:</p>
  <blockquote>
  <p>Cannot resolve the collation conflict between &quot;Latin1_General_BIN&quot; and &quot;SQL_Latin1_General_CP1_CI_AS&quot; in the equal to operation.</p>
  </blockquote>
  <pre><code>SELECT Count(1)
  FROM ServerA.DatabaseA.dbo.TableA
  INNER JOIN ServerB.DatabaseB.dbo.TableB On TableA.ID = TableB.ID;
  </code></pre>
  <p>However, when I run the same query from ServerB it returns without issue.</p>
  <p>Please don't tell me to just use &quot;COLLATE DATABASE_DEFAULT&quot; in the query. I know that. My question isn't necessarily how to resolve the error, it's why running the same query on one server returns the error and not on the other server.</p>