Collation help needed

Question

I have issue with collation on a stored procedure, I think the issue is in this section but I don't know enough about collation to resolve it, ive tried adding it into various sections but it hasn't worked. If it shouldn't go in this section then I can look at the rest of the query as I it should be added anywhere that I have used the temp table?

The collation issue I have is:

conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS"

 create table #tmp_NoRotadayOff (DayOfYear datetime, Rota varchar(10), RotaTemplate varchar(50))

Note: I modified my original answer to remove some of my code, only the bit left was actually affected and caused the issue

Dan Guzman · Accepted Answer · 2014-10-10 14:09:12Z

The default collation for tempdb is the instance collation. You can specify DATABASE_DEFAULT at the column level for temp tables in order to use the current database default collation instead. Try:

CREATE TABLE #tmp_NoRotadayOff ( DayOfYear datetime , Rota varchar(10) COLLATE DATABASE_DEFAULT , RotaTemplate varchar(50) COLLATE DATABASE_DEFAULT );

Fantastic thanks for your help, I had forgot about the database_default syntax but that has worked brilliant

Collectives™ on Stack Overflow

Collation help needed

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related