You were probably trying to do a comparison between 2 databases with different collation settings on a WHERE clause or on a join. Here's how you would do the comparison with a JOIN:
Select *
from source db1.schema.table1 as t1
join source db2.schema.table2 as t2
on t1.col1 collate SQL_Latin1_General_CP1_CS_AS = t2.col1 COLLATE SQL_Latin1_General_CP1_CS_AS