在等于操作中无法解决“SQL_Latin1_General_Pref_CP1_CI_AS”和“Latin1_General_CI_AS”之间的排序规则冲突

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.

无法在等于操作中解决“SQL_Latin1_General_Pref_CP1_CI_AS”和“Latin1_General_CI_AS”之间的排序规则冲突。

The collation of the NavisionMeta database is SQL_Latin1_General_Pref_CP1_CI_AS

NavisionMeta数据库的排序规则是SQL_Latin1_General_Pref_CP1_CI_AS

What can I do to fix this??

我该怎么做才能解决这个问题？

4 个解决方案

#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

我认为你真的应该让所有你的列具有相同的排序规则。我使用这个工具作为数据库我需要将所有varchar列设置为相同的排序规则。 http://www.codeproject.com/KB/database/ChangeCollation.aspx

#2

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

如果a和b是你要比较的两列，而a是与排序SQL_Latin1_General_Pref_CP1_AS，而b是与另一列，你可以说

 ... 
 WHERE a = b COLLATE SQL_Latin1_General_Pref_CP1_AS

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

这会将b转换为指定的排序规则，然后将其与a进行比较。

#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.

可以按列指定排序规则，因此一个或多个* char-type列将与要比较的列具有不同的排序规则。根据treaschf的建议，使用a = b COLLATE SQL_Latin1_General_Pref_CP1_AS或a = b COLLATE Latin1_General_CI_AS。为了获得最大效率，请从您认为具有最多行数的表中选择列的排序规则。这意味着较少的值将在比较期间转换其排序规则。

#4

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.

在将Navision数据的varchar值与非Navision数据进行比较的每种情况下，都必须使用COLLATE子句强制进行排序。

eg, in your example:-

例如，在你的例子中： -

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

#1

#2