How to resolve error Msg 468 : Cannot resolve collation conflict in SQL Server

If you've ever worked with SQL Server you've probably encountered an error like this: 

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

This error is cause by having different collation settings for the columns in the comparison. SQL Server cannot reliably resolve how the columns should be compared to determine if they meet your conditions. For example, when you compare two columns that are both case-sensitive (Latin1_General_CS_AS) then 'AA' = 'AA', but 'Aa' != 'AA'. On a case-insensitive collation 'Aa' = 'AA'.
Where code pages change, or the case or accent sensitivity changes, SQL Server may require you to specify which collation is to be used during comparison.

In this case the error was caused by a comparison of two columns in a join clause where the source tables were from databases with different collations.
Resolving the error is as simple as adding the COLLATE codepage keyword after the column. You can figure out which is the offending column and change its collation, or change both columns to the same collation.

-- Example COLLATE Fix for a JOIN condition
SELECT
   TableA.col1,
   TableB.col2
FROM TableA
INNER JOIN TableB
ON TableA.col3  COLLATE Latin1_General_CI_AS = TableB.col3 COLLATE Latin1_General_CI_AS

-- Example COLLATE Fix for a WHERE condition
SELECT
   TableA.col1
FROM DatabaseA.dbo.TableA, dbo.TableB
WHERE TableA.col3 COLLATE Latin1_General_CI_AS = Max(TableB.col2)


-- Example COLLATE Fix for a WHERE IN condition
SELECT
   TableA.col1
FROM TableA
WHERE TableA.col3 COLLATE Latin1_General_CI_AS IN (SELECT TableB.col1 FROM TableB)

You can check the collation setting for a column using the sp_help TableName command.