在SQL Server上使用不同排序对跨两个数据库执行连接并获得错误

I know, I know with what I wrote in the question I shouldn't be surprised. But my situation is slowly working on an inherited POS system and my predecessor apparently wasn't aware of JOINs so when I looked into one of the internal pages that loads for 60 seconds I see that it's a fairly quick, rewrite these 8 queries as one query with JOINs situation. Problem is that besides not knowing about JOINs he also seems to have had a fetish for multiple databases and surprise, surprise they use different collations. Fact of the matter is we use all "normal" latin characters that English speaking people would consider the entire alphabet and this whole thing will be out of use in a few months so a bandaid is all I need.

我知道，我知道我在这个问题上写的东西，我不应该感到惊讶。但是我的情况慢慢地继承了POS系统的工作和我的前任显然不知道加入所以当我看着一个内部页面加载60秒我看到它是一个相当快,重写这些8查询作为一个查询和连接情况。问题是，除了不知道连接之外，他似乎还对多个数据库有一种癖好，并且对它们使用不同的排序感到惊讶。事实是，我们使用所有的“正常”拉丁字符，英语国家的人会考虑整个字母表，这整个东西将在几个月后停止使用，所以我只需要一个绷带。

Long story short is I need some kind of method to cast to a single collation so I can compare two fields from two databases.

长话短说，我需要某种方法来转换成单个排序，这样我就可以比较两个数据库中的两个字段。

Exact error is:

确切的错误是:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

无法解决“SQL_Latin1_General_CP850_CI_AI”和“SQL_Latin1_General_CP1_CI_AS”之间的排序冲突。

2 个解决方案

#1

109

You can use the collate clause in a query (I can't find my example right now, so my syntax is probably wrong - I hope it points you in the right direction)

您可以在查询中使用collate子句(我现在无法找到我的示例，因此我的语法可能是错误的——我希望它指向正确的方向)

select sone_field collate SQL_Latin1_General_CP850_CI_AI
  from table_1
    inner join table_2
      on (table_1.field collate SQL_Latin1_General_CP850_CI_AI = table_2.field)
  where whatever

#2

A general purpose way is to coerce the collation to DATABASE_DEFAULT. This removes hardcoding the collation name which could change.

一种通用的方法是将排序规则强制到DATABASE_DEFAULT。这将删除可能更改的排序规则名称的硬编码。

It's also useful for temp table and table variables, and where you may not know the server collation (eg you are a vendor placing your system on the customer's server)

它对于临时表和表变量也很有用，您可能不知道服务器排序(例如，您是将系统放在客户服务器上的供应商)

select
    sone_field collate DATABASE_DEFAULT
from
    table_1
    inner join
    table_2 on table_1.field collate DATABASE_DEFAULT = table_2.field
where whatever

#1

109