Cannot resolve the collation conflict between "SQL_Latin1_General_Pref_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

Question

I have the following query:

SELECT 
DISTINCT(po.SONumber) AS [Sales Order No_],
 po.PONumber AS PoNo, ph.[Buy-from Vendor No_] AS VendorNo, 
 ph.[Pay-to Name], ph.[Document Date], 'Ship-to Name' = 
 CASE WHEN sh.[Ship-to Name] > '' THEN sh.[Ship-to Name] ELSE sih.[Ship-to Name] END, 
 'Ship-to Post Code' = CASE WHEN sh.[Ship-to Post Code] > '' THEN sh.[Ship-to Post Code] ELSE sih.[Ship-to Post Code] END, 
 sh.DeliveryPhoneNo AS [Delivery Phone No], 'CustomerPriceGroup' = CASE WHEN sh.[Customer Price Group] > '' THEN sh.[Customer Price Group] ELSE sih.[Customer Price Group] END, 
 'DeliveryComment' = CASE WHEN sh.[Delivery Comment] > '' THEN sh.[Delivery Comment] ELSE sih.[Delivery Comment] END, 
 'GiftMessage' = CASE WHEN sh.[GiftMessage] > '' THEN sh.[GiftMessage] ELSE sih.[GiftMessage] END, 
 si.Shipped, si.ShippedDate, si.CourierID 

 FROM 
 NavisionMeta.dbo.PoToSo po, 
 [Crocus Live$Purchase Header] ph, 
 [Crocus Live$Purchase Line] pl, 
 [Crocus Live$Sales Header] sh, 
 [Crocus Live$Sales Invoice Header] sih, 
 NavisionMeta.dbo.SupplierInput si 

 WHERE po.PONumber = ph.[No_] AND 
 ph.[No_] = pl.[Document No_] AND 
 po.SONumber *= sh.No_ AND 
 po.SONumber *= sih.[Order No_] AND 
 po.PONumber *= si.PONo AND 
 ph.[Document Date] BETWEEN '01-01-10' AND '31-01-10' 

 ORDER BY po.PONumber DESC

When it executes, I get the following error:

Cannot resolve the collation conflict between "SQL_Latin1_General_Pref_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

The collation of the NavisionMeta database is SQL_Latin1_General_Pref_CP1_CI_AS

What can I do to fix this??

Answer 1

I think you really should make all you're columns have the same collation. I used this tool for a db were I needed to set all varchar columns to the same collation. http://www.codeproject.com/KB/database/ChangeCollation.aspx

Answer 2

In every case where you are comparing a varchar value from your Navision data with your non-Navision data, you must force the collation using the COLLATE clause.

eg, in your example:-

...
po.SONumber *= sih.[Order No_] COLLATE SQL_Latin1_General_Pref_CP1_CI_AS AND 
...

Answer 3

Collation can be specified per column, so one or more of your *char-type columns will have a different collation to the column you are comparing to. Use
a = b COLLATE SQL_Latin1_General_Pref_CP1_AS
or
a = b COLLATE Latin1_General_CI_AS
as suggested by treaschf.
For maximum efficiency choose the collation of the column from the table you think will have the most number of rows. This means that less values will have their collation converted during the comparison.

Answer 4

If a and b are two columns you are comparing, and a is with collation SQL_Latin1_General_Pref_CP1_AS, and b is with an other, you can say

 ... 
 WHERE a = b COLLATE SQL_Latin1_General_Pref_CP1_AS

This transforms b to the specified collation, and then compares it with a.