Cannot Resolve Collation Conflict

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)

Resolve collation conflict between Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS

You need to change the collation of one field to match with the collation of the other field.

For example, if column A has collation Latin1_General_CI_AS...

and column B has collation SQL_Latin1_General_CP1_CI_AS...

You could do something like this:

CASE WHEN X > 0 THEN A ELSE B COLLATE Latin1_General_CI_AS END

Or something like like this:

CASE WHEN X > 0 THEN A COLLATE SQL_Latin1_General_CP1_CI_AS ELSE B END

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 Arabic_CI_AS in the equal to operation

It would be better, if you post SQL as a plain text rather than image.

In your case, the issue is in different collations in columns you match in a query. The universal workaround is:

WHERE IFG.ITEM_NAME = I.ITEM_NAME COLLATE database_default

However, consider to make all columns in a database matched to the same collation, collations can be checked this way:

SELECT 
    t.Name 'Table Name',
    c.name 'Col Name',
    c.collation_name
FROM     sys.columns c 
INNER JOIN     sys.tables t ON c.object_id = t.object_id
INNER JOIN     sys.types ty ON c.system_type_id = ty.system_type_id    
WHERE     t.is_ms_shipped = 0

It can be done this way

---

Related Topics