SQL Server 2008 排序规则冲突 - 如何解决？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21433204/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008 Collation conflict - how to resolve?
提问by mtallon
For simplification, POC, I have the following query, using character typed columns:
为了简化，POC，我有以下查询，使用字符类型的列：
select AH_NAME1 from GGIMAIN.SYSADM.BW_AUFTR_KOPF
union
select AH_NAME1 from GGI2014.SYSADM.BW_AUFTR_KOPF
and I get the following error:
我收到以下错误：
Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CS_AS" in the UNION operation.
消息 468，级别 16，状态 9，第 2 行
无法解决 UNION 操作中“SQL_Latin1_General_CP1_CI_AS”和“Latin1_General_CS_AS”之间的排序规则冲突。
GGI2014was indeed created with collation SQL_Latin1_General_CP1_CI_AS. This has been changed in SMS and the instance has been restarted, also in SMS.
GGI2014确实是用 collation 创建的SQL_Latin1_General_CP1_CI_AS。这已在 SMS 中更改并且实例已重新启动，也在 SMS 中。
When I look in SMS, as well as query:
当我查看短信以及查询时：
select name, collation_name from sys.databases
all indications are that both GGIMAINand GGI2014are collated Latin1_General_CS_AS.
所有迹象都表明，GGIMAIN和GGI2014都经过整理Latin1_General_CS_AS。
Does anyone have any advice on what else needs to be done?
有没有人对还需要做的事情有什么建议？
Thanks,
谢谢，
Matt
马特
回答by codeblur
select AH_NAME1 COLLATE DATABASE_DEFAULT from GGIMAIN.SYSADM.BW_AUFTR_KOPF
union
select AH_NAME1 COLLATE DATABASE_DEFAULT from GGI2014.SYSADM.BW_AUFTR_KOPF
Unless I am mistaken, changing the collation of the database does not change the collation of the already existing objects. Only new objects will be affected
除非我弄错了，更改数据库的排序规则不会更改已存在对象的排序规则。只有新对象会受到影响
回答by Devart
Try this one (maybe you're columns have different collation) -
试试这个（也许你的列有不同的排序规则）-
SELECT AH_NAME1 COLLATE database_default
FROM GGIMAIN.SYSADM.BW_AUFTR_KOPF
UNION
SELECT AH_NAME1 COLLATE database_default
FROM GGI2014.SYSADM.BW_AUFTR_KOPF
回答by Brayan Aguilar
I add a collate for each field of the query
我为查询的每个字段添加了一个校对
SELECT Field1 collate default_database
,field2 collate default_database
,fieldn collate default_database
From DB1.dbo.table_x
UNION ALL
SELECT Field1 collate default_database
,field2 collate default_database
,fieldn collate default_database
From DB2.dbo.table_y
PD.only in the query that gives the error
PD.only 在给出错误的查询中
I hope it works and I got them out of trouble
我希望它有效，我让他们摆脱了麻烦
