CODEWITHSUNDEEP
sqlcollation
Justin
Justin

Reputation: 86779

SQL Collation conflict when comparing to a column in a temp table

I have a SQL query that compares a value in the database to a constant:

SELECT * FROM my_table
INNER JOIN #TempTable tem
    ON my_table.id = temp.id
    AND my_table.key = 'SOME STRING'

And I get the error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

How can I get around this? (without making changes to the database)

UPDATE: I get this error even if I remove the last like (the string comparison)...

Upvotes: 16

Views: 38069

Answers (4)

Dimi Takis
Dimi Takis

Reputation: 4959

Specify the collation inside the declaration of your temp table.

CREATE TABLE #TempTable (ID NVARCHAR(255) COLLATE database_default)

Upvotes: 5

Daniel Elliott
Daniel Elliott

Reputation: 22867

The problem is the temp table. It uses the collation of the tempdb.

You could create a table in your actual db and not a temp table and then they would have the same collation. Or specify collation upon creating temp table.

Upvotes: 2

Kev Riley
Kev Riley

Reputation: 711

try

SELECT * FROM my_table
INNER JOIN #TempTable temp    
    ON my_table.id = temp.id collate database_default
    AND my_table.key = 'SOME STRING'

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425683

Seems your id's are VARCHARs with different collations.

Try this:

SELECT  *
FROM    my_table
INNER JOIN
        #TempTable tem
ON      my_table.id = temp.id COLLATE SQL_Latin1_General_CP1_CI_AS
        AND my_table.key = 'SOME STRING'

Upvotes: 22

Related Questions