Temporary Table and Collation Issue?

Question

My server database default collation is Arabic_CI_AS. In my application, I am never setting collation anywhere. But when I run this simple SQL,

CREATE TABLE #TempProductInventory
(
    PID int 
    ,InvTypeValue nvarchar(MAX) 
    ,InvTypeKey nvarchar(MAX) 
                        );

SELECT * 
FROM    #TempProductInventory TP
INNER JOIN dbo.[Sources] S ON (S.Code = TP.InvTypeKey)

I am getting,

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Arabic_CI_AS" in the equal to operation. When I un,

When I run,

SELECT col.name, col.collation_name FROM  sys.columns col WHERE object_id = OBJECT_ID('Sources')

I will get,

name    collation_name
ID         NULL
Code       Arabic_CI_AS

Answer 1

Try this, it will set the collation of temporary table to the one that you specify.
Most likely reason for this happening is as stated by GarethD that temp database collition is set not to Arabic_CI_AS.

CREATE TABLE #TempProductInventory( PID int ,InvTypeValue nvarchar(MAX) ,InvTypeKey nvarchar(MAX) Collate "Arabic_CI_AS")