Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AI” in the equal to operation

Just use the following syntax to collate on the fly when joining tables with different collations. I integrate systems, so I have to do this a lot.

select * from [Product] p join [category] c 
on  
c.[Name] collate SQL_Latin1_General_CP1_CI_AS 
= 
p.[Name] collate SQL_Latin1_General_CP1_CI_AS