SQL 无法解决联合选择中的排序规则冲突
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2673673/
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
Cannot resolve collation conflict in Union select
提问by user278618
I've got two queries:
我有两个疑问：
First doesn't work:
首先不起作用：
select hotels.TargetCode as TargetCode from hotels
union all
select DuplicatedObjects.duplicatetargetCode as TargetCode
from DuplicatedObjects where DuplicatedObjects.objectType=4
because I get error:
因为我得到错误：
Cannot resolve collation conflict for column 1 in SELECT statement.
Second works:
第二部作品：
select hotels.Code from hotels where hotels.targetcode is not null
union all
select DuplicatedObjects.duplicatetargetCode as Code
from DuplicatedObjects where DuplicatedObjects.objectType=4
Structure:
结构：
Hotels.Code -PK nvarchar(40)
Hotels.TargetCode - nvarchar(100)
DuplicatedObjects.duplicatetargetCode PK nvarchar(100)
采纳答案by Joel Goodwin
Use sp_helpon both tables. The collation on hotels.TargetCode is different from the collation on DuplicatedObjects.duplicateTargetCode, so the DB doesn't know what to do with the resulting UNION.
sp_help在两个表上使用。hotel.TargetCode 上的排序规则与 上的排序规则不同DuplicatedObjects.duplicateTargetCode，因此 DB 不知道如何处理生成的UNION.
You can force a new collation on one of them to match the other, or put the results into a predefined temp table/table which will have a collation defined already.
您可以对其中一个强制执行新的排序规则以匹配另一个，或者将结果放入预定义的临时表/表中，该表/表已经定义了排序规则。
EDIT:You can override the existing collation using something like...
编辑：您可以使用类似...
DuplicatedObjects.duplicateTargetCode COLLATE SQL_Latin1_General_CP1_CI_AS
...in the query. This will use the duplicateTargetCode with the collation SQL_Latin1_General_CP1_CI_AS. You should choose a collation which matches that of hotels.TargetCode.
...在查询中。这将使用带有 collation 的重复目标代码SQL_Latin1_General_CP1_CI_AS。您应该选择与hotels.TargetCode.
回答by Jens Busch
You need to add the collationstatement in the select part as well - not only in the where clause - like the following:
您还需要collation在 select 部分添加语句 - 不仅在 where 子句中 - 如下所示：
select a.field1 collate DATABASE_DEFAULT, b.otherfield from table1 a, table2 b
where a.field1 collate DATABASE_DEFAULT = b.field3
回答by Daniel de Zwaan
Trying to set collation in a query when joining a linked server can still fail with Incorrect syntax near 'COLLATE'even though your syntax is correct.
Incorrect syntax near 'COLLATE'即使您的语法正确，在加入链接服务器时尝试在查询中设置排序规则仍然可能会失败。
Solution: In Linked Server Properties, set Use Remote Collationto False, and enter the desired collation type in Collation Name- removes need to force collation in your query.
解决方案：在 Linked Server Properties 中，设置Use Remote Collation为 False，并在其中输入所需的排序规则类型Collation Name- 无需在查询中强制执行排序规则。
回答by Rajnish Kumar
Your collation conflict may go away if you declare the temp table #list as
如果您将临时表 #list 声明为
CREATE TABLE #list
(
record_num INT IDENTITY(1,1),
TempAcctNum NVARCHAR(40) Collate Database_Default,
TempAcctName NVARCHAR(100) Collate Database_Default,
TempNumOfCrds SMALLINT,
TempSys2Acct NVARCHAR(10) Collate Database_Default,
TempDelType TINYINT,
TempStatusOfCrd VARCHAR(100) Collate Database_Default,
TempLastDate VARCHAR(100) Collate Database_Default,
TempSys2Acct1 NVARCHAR(10) Collate Database_Default,
TempShrtName NVARCHAR(50) Collate Database_Default,
TempAdd1 NVARCHAR(200) Collate Database_Default,
TempAdd2 NVARCHAR(200) Collate Database_Default,
TempCity NVARCHAR(100) Collate Database_Default,
TempState NVARCHAR(100) Collate Database_Default,
TempZipCode NVARCHAR(50) Collate Database_Default,
TempOpenDate DATETIME,
TempFax NVARCHAR(50) Collate Database_Default,
TempUsr1 NVARCHAR(100) Collate Database_Default,
TempUsr2 NVARCHAR(100) Collate Database_Default,
TempUsr3 NVARCHAR(100) Collate Database_Default,
TempUsr4 NVARCHAR(100) Collate Database_Default,
TempMemo NTEXT,
TempMail NVARCHAR(100) Collate Database_Default,
TempNoSys2Status NVARCHAR(50) Collate Database_Default,
TempDelete BIT,
TempEdit BIT,
TempContName VARCHAR(200) Collate Database_Default,
TempPhone NVARCHAR(50) Collate Database_Default
)
