If you are ever trying to join tables from different databases that are using different collations you may experience the dreaded…
Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
The fix for this is fairly simple. In the join part of the query around the = parameter you can enter this code which collates the columns of the join as per the database default.
Below is an example of a query with the collate syntax added for your reference.
select * FROM LocalCustomer lc JOIN custtodelete cd on lc.custnumber COLLATE DATABASE_DEFAULT = cd.custnumber COLLATE DATABASE_DEFAULT
I find this way simpler than the other which requires you to specify the actual collation that you wish to use in your join.