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.
set USR_STATUS = 'CC',
END_DATE = dateadd(mm,4,LONG_DESC_02)
where USR_STATUS = 'C'
set USR_STATUS = 'CN',
END_DATE = dateadd(dd,30,LONG_DESC_02)
where USR_STATUS = 'L'
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
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.