Cannot resolve the collation conflict T-SQL

Cannot resolve the collation conflict between XXX and YYY in the equal to operation

I recently got this whilst doing some SQL queries that involved joins between two fields with different (albeit very similar) collation types. To stop SQL server from screaming, you'll need to perform a sort of collation cast. The quickest way is to cast one of the field's collation so it matches the other, or you can cast both. Casting to the default database collation is also an option:

SELECT 
...
JOIN table t ON t.field COLLATE database_default = s.field COLLATE database_default

More info on collate from MSDN.