none
Cannot resolve the collation conflict

    Question

  • i keep getting this error - Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.  Does anyone know who to resolve it, because i am stomped.  here is the cold that I run and I get the error around the 'where ltrim(rtrim(A.ACTIVITY)) = B.ACTIVITY' area.

    update DATA_MIGRATION.dbo.NMF_JOB_CHANGES
    set USR_STATUS = 'CC',
        END_DATE = dateadd(mm,4,LONG_DESC_02)
    where USR_STATUS = 'C'

    update DATA_MIGRATION.dbo.NMF_JOB_CHANGES
    set USR_STATUS = 'CN',
        END_DATE = dateadd(dd,30,LONG_DESC_02)
    where USR_STATUS = 'L'

    update PROD1.dbo.ACACTIVITY
    set LONG_DESC_02 = convert(varchar, B.LONG_DESC_02, 101),
        USR_STATUS = B.USR_STATUS,
        END_DATE = B.END_DATE
    from PROD1.dbo.ACACTIVITY A, DATA_MIGRATION.dbo.NMF_JOB_CHANGES B
    where ltrim(rtrim(A.ACTIVITY)) = B.ACTIVITY
      and A.COMPANY = '13'
      and (A.LONG_DESC_02 = '' or A.LONG_DESC_02 is null)
      and B.LONG_DESC_02 is not null

    Tuesday, March 10, 2009 3:05 PM

Answers

  • You can try explicitly specifying the collation at the quer level. 

    WHERE LTRIM(RTRIM(A.ACTIVITY))COLLATE SQL_Latin1_General_CP1_CI_AS = B.ACTIVITY 
    You will have to see which collation it is on which column that you need to specify

    Hope this helps

    David Dye
    • Marked as answer by risharp Tuesday, March 10, 2009 9:47 PM
    Tuesday, March 10, 2009 3:11 PM

All replies

  • You can try explicitly specifying the collation at the quer level. 

    WHERE LTRIM(RTRIM(A.ACTIVITY))COLLATE SQL_Latin1_General_CP1_CI_AS = B.ACTIVITY 
    You will have to see which collation it is on which column that you need to specify

    Hope this helps

    David Dye
    • Marked as answer by risharp Tuesday, March 10, 2009 9:47 PM
    Tuesday, March 10, 2009 3:11 PM
  • Thanks that worked perfectly
    Tuesday, March 10, 2009 9:47 PM
  • The answer was spot on, but you should really figure out why you are in this condition.  There is very little need to have databases on a server in different collations (note before the flames start, there are lots of reasons, just not that many cases where it makes sense) and this is the kind of thing that comes up when you do. 

    Obviously you are probably stuck with things as is, but if you have a prod db and a data migration database with different collations, you are asking for little problems to creep up and bite you.  This is particularly true when using a Binary collation and a normal case insensitive one.  If you need the strictness of case insensitivity/binary sort order, things that are unique in one db will no longer be unique in the other.
    http://drsql.spaces.msn.com
    Tuesday, March 10, 2009 10:38 PM