在SQL Server上使用不同排序规则的两个数据库之间进行联接并收到错误 [英] Doing a join across two databases with different collations on SQL Server and getting an error

问题描述

我知道，对于我在问题中所写的内容，我都不会感到惊讶。但是我的情况是在继承的POS系统上缓慢地工作，而我的前任显然没有意识到JOIN，因此当我查看载入60秒的内部页面之一时，我发现这相当快，将这8个查询重写为一个用JOINs查询情况。问题在于，除了不了解JOIN之外，他似乎还迷恋多个数据库，并且感到惊讶，他们惊讶地使用了不同的排序规则。事实是，我们使用所有普通拉丁字符，说英语的人会考虑整个字母，而整个事情将在几个月内用完，所以我只需要创可贴即可。

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.

长话短说，我需要某种方法强制转换为单个排序规则，以便可以比较两个数据库中的两个字段。

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.

精确错误是：

无法解决
SQL_Latin1_General_CP850_CI_AI和
SQL_Latin1_General_CP1_CI_AS之间的排序规则冲突
在
中等于操作。

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

推荐答案

您可以在查询（我现在找不到我的示例，所以我的语法可能是错误的-我希望它指向正确的方向）

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)

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

这篇关于在SQL Server上使用不同排序规则的两个数据库之间进行联接并收到错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！