Cannot resolve the collation conflict between “SQL

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 Server 2008 and have restored a copy of this db on to my machine which is 2005.

Answer 1

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 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

Thanks to marc_s's answer I solved my original problem - inspired to take it a step further and post one approach to transforming a whole table at a time - tsql script to generate the alter column statements:

DECLARE @tableName VARCHAR(MAX)
SET @tableName = 'affiliate'
--EXEC sp_columns @tableName
SELECT  'Alter table ' + @tableName + ' alter column ' + col.name
        + CASE ( col.user_type_id )
            WHEN 231
            THEN ' nvarchar(' + CAST(col.max_length / 2 AS VARCHAR) + ') '
          END + 'collate Latin1_General_CI_AS ' + CASE ( col.is_nullable )
                                                    WHEN 0 THEN ' not null'
                                                    WHEN 1 THEN ' null'
                                                  END
FROM    sys.columns col
WHERE   object_id = OBJECT_ID(@tableName)

gets: ALTER TABLE Affiliate ALTER COLUMN myTable NVARCHAR(4000) COLLATE Latin1_General_CI_AS NOT NULL

I'll admit to being puzzled by the need to col.max_length / 2 -

Answer 4

I do the following:

...WHERE 
    fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT

Works every time. :)

Answer 5

In the where criteria add collate SQL_Latin1_General_CP1_CI_AS

This works for me.

WHERE U.Fullname = @SearchTerm  collate SQL_Latin1_General_CP1_CI_AS