Collation Sequence difference between table variables and temp tables

Question

Collation Sequence difference between table variables and temp tables

Tom257

SSChampion

Points: 11543
More actions
November 4, 2011 at 7:01 am

#259543

I have a query developed on one server, which runs fine.
However when I try to run it on another SQL 2005 server, it barfs with
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
No COLLATE clause is used in the query or table definitions
In the query where it goes wrong, there is a temporary table joined to a table variable.
I Checked: tempdb has SQL_Latin1_General_CP1_CI_AS and the db I'm using has Latin1_General_CI_AS
It looks like the table variable uses the properties of the current db, rather than tempdb.
Is there a way to make all temporary objects use the same collation sequence within a script - some SET option perhaps

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 1

If all the non-tempdb databases use the same collation but is different than TempDB, wouldn't it be easier to just change the default collation of TempDB?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tom257 SSChampion Points: 11543 More actions · Answer 2

In a perfect world, yes.

However we have this DBA.... to cut a long story short ... we don't even bother asking any more.

Ken McKelvey SSCoach Points: 18843 More actions · Answer 3

The best way to ensure the correct collation for a temp table is to always use SELECT INTO to create them.

(This also has the advantage that if the column width changes in the original table it is reflected in the temp table.)

eg

SELECT *

INTO #temp

FROM YourTable

The alternative is to explicitly declare the default db collation for text columns when creating temp tables.

CREATE TABLE #temp

(

id int NOT NULL

,TestText varchar(20) COLLATE DATABASE_DEFAULT NOT NULL

)