To resolve collation conflicts like:

Msg 468, Level 16, State 9, Line 135
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

You should surround the ‘=’ with collate database_default

select * from Product p
inner join Sales s
where p.Name collate database_default = s.ProductName collate database_default

Solution found at: http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/