GOODS (9/19/2013)
Hi guys,I'm have same issue:
Msg 468, Level 16, State 9, Procedure RPT_MTX_CDR_CostBreakdown_PerMsisdn, Line 142
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
I have the query below and my error massage is pointing at Line 142 and Line 142 is the SELECT DISTINCT.Please assist.
Select distinct ---------line 142
aa.Msisdn,
bb.box_no
into #MSISDNUnit
from #AllMSISDNCost aa
left join [10.24.4.56\EGHI].wasp_administration_SS.dbo.z_Unit_Lookup bb
on aa.MSISDN COLLATE DATABASE_DEFAULT = bb.MSISDN COLLATE DATABASE_DEFAULT
where bb.msisdn in (Select distinct msisdn from #AllMSISDNCost)
order by bb.box_no--24202
If you need to force the collation on the ON condition you also need to enforce it on the WHERE condition, as the columns involved are the same; so you could make it bb.MSISDN COLLATE DATABASE_DEFAULT in (Select distinct msisdn COLLATE DATABASE_DEFAULT from #AllMSISDNCost) .
Are you running with ANSI NULLs switched off, so that NULL in (select NULL) would deliver true?
Or are you working in a case sensitive system so that one (or both) of #AllMSISDNCost and z_Unit_Lookup has two columns, one called MSISDN and the other msisdn?
If neither of the above rather unusual things is true, do you think that filtering the result of left join on the equality condition by applying that where clause will produce something which would not be achieved by simplifying of the query to use an inner join and throwing away the where clause?
Tom