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

Question

I have the following code

SELECT tA.FieldName As [Field Name],
COALESCE(tO_A.[desc], tO_B.[desc], tO_C.Name, tA.OldVAlue) AS [Old Value],
COALESCE(tN_A.[desc], tN_B.[desc], tN_C.Name, tA.NewValue) AS [New Value],
U.UserName AS [User Name],
CONVERT(varchar, tA.ChangeDate) AS [Change Date] 
FROM D tA
JOIN [DRTS].[dbo].[User] U ON tA.UserID = U.UserID
LEFT JOIN A tO_A on tA.FieldName = 'AID' AND tA.oldValue = CONVERT(VARCHAR, tO_A.ID)
LEFT JOIN A tN_A on tA.FieldName = 'AID' AND tA.newValue = CONVERT(VARCHAR, tN_A.ID)
LEFT JOIN B tO_B on tA.FieldName = 'BID' AND tA.oldValue = CONVERT(VARCHAR, tO_B.ID)
LEFT JOIN B tN_B on tA.FieldName = 'BID' AND tA.newValue = CONVERT(VARCHAR, tN_B.ID)
LEFT JOIN C tO_C on tA.FieldName = 'CID' AND tA.oldValue = tO_C.Name
LEFT JOIN C tN_C on tA.FieldName = 'CID' AND tA.newValue = tN_C.Name
WHERE U.Fullname = @SearchTerm
ORDER BY tA.ChangeDate

When running the code I am getting the error pasted in the title after adding the two joins for table C. I think this may have something to do with the fact i'm using sql 2008 and have restored a copy of this db on to my machine which is 2005. Please help!

Answer 1

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)

Answer 2

I have had something like this before, and what we found was that the collation between 2 tables were different.

Check that these are the same.

Answer 3

Use the 'collate' clause in your query:

LEFT JOIN C tO_C on tA.FieldName = 'CID' AND tA.oldValue COLLATE Latin1_General_CI_AS = tO_C.Name

I may not have the syntax exactly right (check BOL), but you can do this to change the collation on-the-fly for the query - you may need to add the clause for each join

edit: I realized this was not quite right - the collate clause goes after the field you need to change - in this example I changed the collation on the tA.oldValue field

Answer 4

I do the following:

... WHERE fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT

Works every time. :)

HTH!

Valkyrie

Answer 5

The root cause is that the sql server database you took the schema from has a collation that differs from your local installation. If you don't want to worry about collation re install SQL Server locally using the same collation as the SQL Server 2008 database.