Sql Collation Conflict When Comparing to a Column in a Temp Table

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

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'

Collation conflict with temp table

Check the tables involved as well:

SELECT name, collation_name, OBJECT_NAME(object_id)
FROM sys.columns 
WHERE OBJECT_NAME(object_id) IN ('Cities')

Temp Table collation conflict - Error : Cannot resolve the collation conflict between Latin1* and SQL_Latin1*

This happens because the collations on #tempdb.temp_po.OD1 and STR_IndentDetail.D1 are different (and specifically, note that #tempdb is a different, system database, which is generally why it will have a default opinion for collation, unlike your own databases and tables where you may have provided more specific opinions).

Since you have control over the creation of the temp table, the easiest way to solve this appears to be to create *char columns in the temp table with the same collation as your STR_IndentDetail table:

CREATE TABLE #temp_po(
    IndentID INT, 
    OIndentDetailID INT, 
    OD1 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS, 
    .. Same for the other *char columns   

In the situation where you don't have control over the table creation, when you join the columns, another way is to add explicit COLLATE statements in the DML where errors occur, either via COLLATE SQL_Latin1_General_CP1_CI_AS or easier, using COLLATE DATABASE_DEFAULT

SELECT * FROM #temp_po t INNER JOIN STR_IndentDetail s 
   ON t.OD1 = s.D1 COLLATE SQL_Latin1_General_CP1_CI_AS;

OR, easier

SELECT * FROM #temp_po t INNER JOIN STR_IndentDetail s 
   ON t.OD1 = s.D1 COLLATE DATABASE_DEFAULT;

SqlFiddle here

Cannot resolve the collation conflict error with Temp Table

A collation defines a way to compare strings. Whenever you do a comparison between string types (ordering requires comparison too) you need to apply the same criteria, this is the collation type.

CREATE TABLE #StringLatin (
    String VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS)

CREATE TABLE #StringBIN (
    String VARCHAR(100) COLLATE Latin1_General_BIN)

SELECT
    'Oops!!!'
FROM
    #StringLatin AS L
    INNER JOIN #StringBIN AS B ON L.String = B.String

Msg 468, Level 16, State 9, Line 5 Cannot resolve the collation
conflict between "Latin1_General_BIN" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

The problem here is the L.String = B.String, since both were created with different collation types.

You can solve this by either changing the collation at the table's creation statement (so it matches whichever you are joining against later) or directly at the comparison, inside the SELECT.

SELECT
    'Works'
FROM
    #StringLatin AS L
    INNER JOIN #StringBIN AS B ON L.String = B.String COLLATE SQL_Latin1_General_CP1_CI_AS

The default collation can change from server to server and it will affect all tables that don't specify a particular collation, which can bring trouble when working with different databases. To find the default collation you can use:

SELECT SERVERPROPERTY('collation');

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

You have a mismatch of two different collations in your table. You can check what collations each column in your table(s) has by using this query:

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

Collations are needed and used when ordering and comparing strings. It's generally a good idea to have a single, unique collation used throughout your database - don't use different collations within a single table or database - you're only asking for trouble....

Once you've settled for one single collation, you can change those tables / columns that don't match yet using this command:

ALTER TABLE YourTableName
  ALTER COLUMN OffendingColumn
    VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL

Marc

UPDATE: to find the fulltext indices in your database, use this query here:

SELECT
    fti.object_Id,
    OBJECT_NAME(fti.object_id) 'Fulltext index',
    fti.is_enabled,
    i.name 'Index name',
    OBJECT_NAME(i.object_id) 'Table name'
FROM 
    sys.fulltext_indexes fti
INNER JOIN 
    sys.indexes i ON fti.unique_index_id = i.index_id

You can then drop the fulltext index using:

DROP FULLTEXT INDEX ON (tablename)

Collation Conflict

It's probably either:

1. There are some specific character-typed columns or tables created with a different COLLATION, then you're doing a comparison on columns of different collation.

2. You're running a script against a database that's a different collation to your server, and the script uses TEMP TABLEs. Temp tables are created in tempdb database, therefore they get the COLLATION of tempdb (ie the server collation). Most scripts don't expect this, so comparisons against temp table columns don't specify a collation.

You can fix this either by changing your tables/columns so the collations match, or changing your SQL so that you specify which collation to use in each string comparison.

e.g. change this:

select * 
from someTable 
inner join #t on someTable.col1 = #t.col1
where #t.col2 = 'blah' collate database default

to this:

select * 
from someTable 
inner join #t on someTable.col1 = #t.col1 collate database default
where #t.col2 = 'blah' collate database default

UPDATE:

Based on the script you've posted, I'd guess it's my second suggestion above.

SQL Server is saying the problem is in your last massive query. I suggest one or more of the following:

• To identify the particular query that's a problem I'd suggest you break out the subqueries and have the script execute
  them separately. Just for debugging.

• Try calling sp_help '##temp1' and for the other tables after they're created, and compare the column collations with those in your main tables.

• Add collate database default to the end of each of your comparisons (where needed if you use the sp_help suggestion)

• Create your temp tables explicitly instead of via SELECT INTO. That way you can specify the columns' collation. Or use table variables instead.

collation conflict between Hebrew_CI_AS and SQL_Latin1_General_CP1_CI_AS

Temporary tables are created using the server's collation by default. It looks like your server's collation is SQL_Latin1_General_CP1_CI_AS and the database's (actually, the column's) Hebrew_CI_AS or vice versa.

You can overcome this by using collate database_default in the temporary table's column definitions, eg :

create #x (
    ID int PRIMARY KEY,
    Company_Code nvarchar(20) COLLATE database_default,
    Cust nvarchar(20) COLLATE database_default,
    ...
)

This will create the columns using the current database's collation, not the server's.

Collation with union and tempdb

If you explicitly set the collation in the union it should be fine:

select grade_name collate Cyrillic_General_CI_AI from [mybase].[DDS].[testtable]
union
select a collate Cyrillic_General_CI_AI from [sysdb].[##temp table];

Note that columns can have a collation different from the database.

---

Related Topics