I'm trying to query between 2 servers with different collations. One query works, the other doesn't. I want to know why.
ServerA is 2019 RTM using Latin1_General_BIN collation.
ServerB is 2016 SP1 using SQL_Latin1_General_CP1_CI_AS collation.
The linked server properties for both servers (the link to ServerB from ServerA and vice versa) are identical, including the 'Use Remote Collation' setting, which is set to False. 'Collation Compatible' is False, and 'Collation Name' is blank.
When I run this query from ServerA I get the error:
Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
SELECT Count(1)
FROM ServerA.DatabaseA.dbo.TableA
INNER JOIN ServerB.DatabaseB.dbo.TableB On TableA.ID = TableB.ID;
However, when I run the same query from ServerB it returns without issue.
Please don't tell me to just use "COLLATE DATABASE_DEFAULT" 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.