title: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation
body: <p>I have the following code</p>
  
  <pre><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
  </code></pre>
  
  <p>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.</p>