归类在连接不同数据库中的表时发生冲突 [英] Collation Conflict when joining tables in different databases

问题描述

大家好，我必须在不同数据库的两个表之间执行连接。在开发机器上工作时，一切正常，直到我在客户端服务器上部署我的数据库。客户端数据库采用不同的排序规则... latin1_General_100_CI_AS。虽然我的数据库是默认的latin1_General_CI_AS。

这是我数据库中的表格

 列名称整理
 QuestionGroupCombination Latin1_General_CI_AS 
 QuestionGroup Latin1_General_CI_AS 
 Company Latin1_General_CI_AS 
 DepartmentCode  NULL  
 Position Latin1_General_CI_AS 
 Weightage  NULL  
 UpdatedBy Latin1_General_CI_AS 

这是我要执行连接的表...它位于不同的数据库上

代码Latin1_General_100_CI_AS 
名称Latin1_General_CI_AS 
维度代码Latin1_General_CI_AS 

我想在两个表之间的Departmentcode和Code列之间执行连接。

这是我的sql查询

 选择  distinct  q.QuestionGroupCombination，q.QuestionGroup + '   - ' + u.Name + '   - ' + q.Position  as  CombinedExpr 来自 QuestionGroup  as  q 
  inner   join  [NAV-DB]。[dbo]。[vw_Department]  as  u  on  q.DepartmentCode = u.Code 

此查询引发以下错误：



无法执行varchar值到varchar的隐式转换，因为值的排序规则未解决整理冲突。



另外，要注意的是客户端表中的代码字段是varchar（20）类型，在我的表格中，DepartmentCode字段是一个int字段。



i已经尝试了各种帖子中建议的所有内容...我尝试更改我的数据库整理...我改变了查询''=''符号作为两侧的Collat​​e Latin1_General_100_CI_AS，只有一方......然后它给出了错误，表达式int对collat​​e子句无效....我甚至改变了数据类型在我的桌子上的字段..等等。
所以，我怎么能解决这个问题？...任何人都可以帮忙...... PLZ



编辑：DepartmentCode（varchar（20））...我更改了数据类型并且不再为null ...它显示Latin1_General_100_CI_AS ...但仍然抛出上述错误...如果有可能我想保留数据类型为int ...因为我必须更改lotta代码

谢谢

解决方案

环顾这里： http://www.sqlusa.com/bestpractices2005/concatenationcolla tionconflict / [ ^ ]

关键是，你不能连接不同排序规则的varchar字段，因为（至少）结果排序规则是未定义的。因此，您只需要确定作为连接结果所需的归类结果，并更改至少其中一个选择的归类。当您需要使用不同的排序规则加入文件时也会出现这种情况：更改排序规则。

以下语句应该有效：

 选择  distinct  
 q.QuestionGroupCombination，q.QuestionGroup + '   - ' + u.Name + '   - ' + q.Position  as  CombinedExpr 
 来自 QuestionGroup < span class =code-keyword> as  q 
  inner   join  [ NAV-DB]。[dbo]。[vw_Department]  as  u  on  q.DepartmentCode  < span class =code-keyword> COLLATE  DATABASE_DEFAULT  = u.Code   COLLATE  DATABASE_DEFAULT  

我在这里写这个，以便任何寻找类似问题的解决方案的人都可以稍后查看它。

这里是保存我的代码....感谢 Zoltan Zorgo

 选择  distinct  q.QuestionGroupCombination，q.QuestionGroup + '   - ' + u.Name < span class =code-keyword> COLLATE  DATABASE_DEFAULT + '   - ' + q。将定位为 CombinedExpr 来自 QuestionGroup  as  q 
 内部  join  [NAV-DB]。[dbo]。[vw_Department]  as  u  on  q.DepartmentCode  COLLATE  DAT ABASE_DEFAULT = u.Code  COLLATE  DATABASE_DEFAULT 

上面提供的链接也是非常有用......请查看。

Hello everyone, i have to perform a join between two tables which are on different databases. When working on the development machine everything was working fine until i deployed my database on the clients server. The Clients database is in a different collation...latin1_General_100_CI_AS.While my database is the default latin1_General_CI_AS.
This is the table in my database

Column Name                     Collation
QuestionGroupCombination	Latin1_General_CI_AS
QuestionGroup	Latin1_General_CI_AS
Company	Latin1_General_CI_AS
DepartmentCode	NULL
Position	Latin1_General_CI_AS
Weightage	NULL
UpdatedBy	Latin1_General_CI_AS

This is the table i want to perform the join on...which is on a different database

Code	Latin1_General_100_CI_AS
Name	Latin1_General_CI_AS
Dimension Code	Latin1_General_CI_AS

I want to perform join between the Departmentcode and Code columns between the two tables.
Here is my sql query

select distinct q.QuestionGroupCombination,q.QuestionGroup+'--'+u.Name+'--'+q.Position as CombinedExpr from QuestionGroup as q
                                         inner join [NAV-DB].[dbo].[vw_Department] as u on q.DepartmentCode = u.Code

This query throws the following error:

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

Also, a thing to note is that the code field in clients table is a varchar(20) type,while the DepartmentCode field is an int field in my table.

i have tried everything suggested in various posts...i tried changing my database collation...i changed the query on the ''='' sign as Collate Latin1_General_100_CI_AS on both sides,only one side......then it gives me error saying expression int is invalid for collate clause....i have even changed the datatype of the Field in my table..etc
so, how can i resolve this issue??...Can anyone help...plz

EDIT: The DepartmentCode(varchar(20))...i changed the datatype and is no longer null...it shows Latin1_General_100_CI_AS...but still throws the above error... if it is possible i want to keep the datatype as int...since i have to change a lotta code
Thanks

解决方案

Look around here: http://www.sqlusa.com/bestpractices2005/concatenationcollationconflict/[^]
The key point is, that you can not concatenate varchar fields from different collation, since (at least) the result collation would be undefined. So, you only need to figure out what resulting collation do you need as the concatenation result and change collation on select for at least one of them. This is also the case when you need to join on fileds with different collation: change collation in place.
Following statement should work:

select distinct 
q.QuestionGroupCombination,q.QuestionGroup+'--'+u.Name+'--'+q.Position as CombinedExpr 
from QuestionGroup as q
inner join [NAV-DB].[dbo].[vw_Department] as u on q.DepartmentCode COLLATE DATABASE_DEFAULT = u.Code COLLATE DATABASE_DEFAULT

i am writting this here so that anyone looking for a solution to similar problem can later view it.
here is the code that saved me....Thanks to Zoltan Zorgo

select distinct q.QuestionGroupCombination,q.QuestionGroup+'--'+u.Name COLLATE DATABASE_DEFAULT+'--'+q.Position as CombinedExpr from QuestionGroup as q
                                         inner join [NAV-DB].[dbo].[vw_Department] as u on q.DepartmentCode COLLATE DATABASE_DEFAULT=u.Code COLLATE DATABASE_DEFAULT

The Link Provided above is also very useful...please view it.

这篇关于归类在连接不同数据库中的表时发生冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！