sql-server - 在等于操作中无法解决“SQL_Latin1_General_CP1_CI_AS”和“Latin1_General_CI_AS”之间的排序规则冲突(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 Server 2008 and have restored a copy of this db on to my machine which is 2005.

(在运行代码时，我在为表C添加两个连接后得到了标题中粘贴的错误。我认为这可能与我使用SQL Server 2008的事实有关，并已将此数据库的副本还原到我的机器是2005年。)

  ask by jhowe translate from so

Answer 1

I do the following:

(我做以下事情：)

...WHERE 
    fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT

Works every time.

(每次都有效。)

:)

(:))