Collation precedence conflict in SQL Server 2000
The collation precedence rules apply only to the character string data types, char, varchar, text, nchar, nvarchar, and ntext. Objects with other data types do not participate in collation evaluations.
link to article on msdn
A simple example is as follows
create table #TempRO
(
Id int Identity (1,1),
App_No NVarchar (100)
)
– insert some values in this table
Select *
from #TempRO T
Inner join App AO
On T.App_No = AO.App_No
We are having the App table using a different collation then the default that is “Arabic_CI_AS”
So the above select will give the following collation error, to remove it we just add the following statement while defining the temp table
create table #TempRO
(
Id int Identity (1,1),
App_No NVarchar (100) collate Arabic_CI_AS
)
This not only happens in temp tables but also in the normal tables or at times if we are comparing columns so its better to use sp_help TableName or the syscolumns to find the collation of the two.
Happy Collating 
