CODEWITHSUNDEEP
sqlsql-server-2012collationcollate
ERJAN
ERJAN

Reputation: 24500

collate "sql_latin" and "latin1" mismatch conflict in union only - how to resolve?

using ms sql server, migrating query that pulls data from various tables. There is no joins , only union all.

It still gives me the error, even though i explicitly try to use same collate std - neither works.

"Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in UNION ALL operator occurring in SELECT statement column 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]

the problem comes from the table DimWorkSchedule - it has diff standard for collation.

Upvotes: 0

Views: 1305

Answers (1)

ERJAN
ERJAN

Reputation: 24500

the problem came from 3 columns in dimwork schedule

this showed me collate standards, and the first ID column had a diff one.

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

this is final query , i collated all of them like this:

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

Upvotes: 2

Related Questions