Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Issue -Msg 468, Level 16, State 9, Line 32
Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Issue Description -While working with sql i was fetching records from two different databases based on join condition


select T.Column1,t1.column2 from  Test.dbo.table1 T
join Test1.dbo.Table1 T1 on T.id=t1.id


error

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

So i checked the collation of database using below command
SELECT CONVERT (nvarchar, SERVERPROPERTY('collation'));

If you want to see collation of all the Databases run below command

SELECT name, collation_name FROM sys.databases;

I checked both the databases were using different collation.

So here is the solution

Just add collate Database_default in your join condition as below

select T.Column1,t1.column2 from  Test.dbo.table1 T
join Test1.dbo.Table1 T1 on T.id COLLATE DATABASE_DEFAULT =t1.id COLLATE DATABASE_DEFAULT

Note: You can face this type of errors when your fetching records from different databases having different collation
even if you are using temp table you can also face the same issue as temp tables are stored in temp db database and if
your database collation doesn't match with temp db database it will throw above error.
Collation affect join conditions,where,functions and temporary tables.