I have run into collation issues between tempdb and the database.
tempdb - collation is SQL_Latin1_General_CP1_CI_AS
Database(xxx) is Latin1_General_CI_AS
My Sp errors out reporting the collation conflict when a join is based on string fields.
I have temperorily fixed this issue by forcing my temp table to use the database xxx collation.
CREATE TABLE #BusinessDate
RequestID VARCHAR(10) COLLATE Latin1_General_CI_AS,
FileName VARCHAR(100) COLLATE Latin1_General_CI_AS,
BusinessDate VARCHAR(8) COLLATE Latin1_General_CI_AS
And Now my Sp works correctly.
Is there a better solution where i can define in my sp to force the collation to the database default once rather than defining for every column of the temp table?
Thanks in advance