整理"sql_latin“和"latin1”错配冲突仅限于联合--如何解决？

Question

使用ms sql server，迁移从各种表中提取数据的查询。没有加入，只有团结。

它仍然给了我错误，即使我显式地尝试使用相同的整理std --两者都不起作用。

“无法解决UNION操作符中的"SQL_Latin1_General_CP1_CI_AS”和"Latin1_General_CI_AS“之间的排序规则冲突，它们发生在SELECT语句列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

这个问题来自于酒窝计划中的三列。

这显示了我整理的标准，第一个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