Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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??

like image 457
Alex Avatar asked Jan 12 '10 10:01

Alex


People also ask

How do you solve Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS in the equal to operation?

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.

How do you resolve a collation conflict in a union?

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.

Is SQL_Latin1_General_CP1_CI_AS the same as Latin1_General_CI_AS?

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.

How do I change the temp table collation in SQL Server?

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.


1 Answers

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.

like image 143
treaschf Avatar answered Oct 14 '22 11:10

treaschf