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??
Issue: Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” Simply apply the default collation to the fields you are comparing.
COLLATE can be used at the database level or the column level. Since you are trying to UNION two tables, using the column collation on the needed columns will resolve your query. Of course, if you have several columns with conflicting collations you will need to define their collations as well.
The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.
If you create a temporary table, the columns in the temporary table get the default collation of tempdb (which is also the default collation of master and model). However, you can use DATABASE_DEFAULT to force the collation to match the default collation of the current database.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With