collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in UNION ALL operator.

Msg 457, Level 16, State 1, Line 15 Implicit conversion of char value to char cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in UNION ALL operator.

   
Union ALL across 2 databases was failing with above error.


solution is to identify the column which is causing this issue and use collate database_default

select ColumnCasuingIssues collate database_default 
from myDatabase1.dbo.MyTable1

Union ALL

select ColumnCasuingIssues collate database_default 
from myDatabase2.dbo.MyTable1