[SQL] SQL Server change collation during query

When working with multiple Microsoft SQL Server databases, you may encounter an error which states something like "Cannot resolve collation conflict for equal to operation".

This happens when the two databases have different collations and you try to compare strings between them.

Now, you can change the server collation, database collation or column collation but it won't immediately solve the problem. If you just need to query data combining both databases, you can always use the COLLATE keyword:

SELECT * from DB1.DBO1.TABLE t1, DB2.DBO2.TABLE2 t2
WHERE t1.field=t2.field COLLATE [collation_name];