SQL Collation issue in temp table.

When you create a new temp table and join it with existing tables. you may got this error "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.", 

This because the default collation in Server defferent from the collation in the tables.

To avoid this error, you can simply change the definition for the temp table as follows,

 

CREATE TABLE #tmp(NcesSchoolId NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS
				, MSRRank INT
				, SchoolNum INT)