整理"sql_latin"，仅在联合中"latin1"不匹配冲突 - 如何解决？

Question

使用ms-sql服务器，迁移从各种表中提取数据的查询。没有联接，只有联合。

尽管我明确地尝试使用相同的collate std，但它仍然给了我错误——两者都不起作用。

"无法解决之间的排序规则冲突"SQL_Latin1_General_CP1_CI_AS"；以及"；Latin1_ General_CI_；在UNION ALLSELECT语句列2中出现的运算符。

SELECT PHonetype_PHK AS  [ID], 
PhoneTypeCode                            AS Code, 
PhoneTypeName                            AS Caption, 
'PhoneType'  COLLATE DATABASE_DEFAULT AS PSWSEnumType,  --COLLATE SQL_Latin1_General_CP1_CI_AS AS PSWSEnumType, --COLLATE latin1_general_ci_as AS PSWSEnumType,
GETDATE() AS [DFLastDateTime]
FROM   DWH.DimPhoneType 
UNION ALL 
SELECT [ID], 
WorkScheduleCode                            AS Code, 
WorkScheduleName                            AS Caption, 
'WorkSchedule' COLLATE DATABASE_DEFAULT AS PSWSEnumType,  -- AS PSWSEnumType, --COLLATE latin1_general_ci_as AS PSWSEnumType, COLLATE DATABASE_DEFAULT
GETDATE() AS [DFLastDateTime]
FROM   DWH.DimWorkSchedule 

UNION ALL 
SELECT 'S', 
'S'                                       AS Code, 
'Contractor'                              AS Caption, 
'SearchType' AS PSWSEnumType, --COLLATE Latin1_General_CI_AS AS PSWSEnumType,
GETDATE() AS [DFLastDateTime]

问题来自DimWorkSchedule表——它有不同的排序标准。

Answer 1

问题来自dimwork计划中的3列

这让我看到了整理标准，第一个ID列有一个不同的。

select col.collation_name  FROM SYS.columns   col WHERE   object_id = OBJECT_ID('DWH.DimWorkSchedule')

这是最后一个查询，我像这样整理了所有的：

SELECT [ID]                  COLLATE SQL_Latin1_General_CP1_CI_AS                        AS ID, 
WorkScheduleCode      COLLATE SQL_Latin1_General_CP1_CI_AS                      AS Code, 
WorkScheduleName      COLLATE SQL_Latin1_General_CP1_CI_AS                      AS Caption, 
'WorkSchedule'                             COLLATE SQL_Latin1_General_CP1_CI_AS AS PSWSEnumType,  -- AS PSWSEnumType, --COLLATE latin1_general_ci_as AS PSWSEnumType, COLLATE DATABASE_DEFAULT
GETDATE() AS [DFLastDateTime]
FROM   DWH.DimWorkSchedule