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

Identify the fields for which it is throwing this error and add following to them: COLLATE DATABASE_DEFAULT

There are two tables joined on Code field:

...
and table1.Code = table2.Code
...

Update your query to:

...
and table1.Code COLLATE DATABASE_DEFAULT = table2.Code COLLATE DATABASE_DEFAULT
...

Answer 2

ALTER DATABASE test2 -- put your database name here COLLATE Latin1_General_CS_AS -- replace with whatever collation you need

Answer 3

here is what we did, in our situation we need an ad hoc query to be executed using a date restriction on demand, and the query is defined in a table.

Our new query needs to match data between different databases and include data from both of them.

It seems that the COLLATION is different between the db that imports data from the iSeries/AS400 system, and our reporting database - this could be because of the specific data types (such as Greek accents on names and so on).

So we used the below join clause:

...LEFT Outer join ImportDB..C4CTP C4 on C4.C4CTP COLLATE Latin1_General_CS_AS=CUS_Type COLLATE Latin1_General_CS_AS

Answer 4

To resolve this problem in the query without changing either database, you can cast the expressions on other side of the "=" sign with

COLLATE SQL_Latin1_General_CP1_CI_AS

as suggested here.

Answer 5

I had a similar error (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1250_CI_AS" in the INTERSECT operation), when I used old jdbc driver.

I resolved this by downloading new driver from Microsoft or open-source project jTDS.

Answer 6

@Valkyrie awesome answer. Thought I put in here a case when performing the same with a subquery insides a stored procedure, as I wondered if your answer works in this case, and it did awesome.

...WHERE fieldname COLLATE DATABASE_DEFAULT in ( select distinct otherfieldname COLLATE DATABASE_DEFAULT from ... where ... )