How resolve table collation mismatch problems in join and union

Question

When working with the Table1 and the Table2 or Table3 tables, I’m getting the following error

Microsoft SQL: Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in UNION ALL operator for DISTINCT operation.

DataBase Collation  is  SQL_Latin1_General_CP1_CI_AS    

Table1  Collation  is   SQL_Latin1_General_CP1_CI_AS    
Table2  Collation  is   SQL_Latin1_General_CP1_CI_AS
Table3  Collation  is   SQL_Latin1_General_CP1_CI_AS


SELECT
      b.[BudgetID]
      ,b.[Budget]
      ,b.[BudgetType]
      ,b.[BudgetStatus]
      ,b.[BudgetStartDate]
      ,b.[BudgetEndDate]
      ,b.[ActualStartDate]
      ,b.[ActualEndDate]
      ,b.[PlannedBudget]
      ,b.[ActualSpend]
      ,b.[ProductLineName]
      ,b.[ProductLineKey]
      ,b.[ProductName]
      ,b.[ProductKey]
      ,b.[PlannedLeads]
      ,b.[ActualLeads]

  FROM [dbo].[Table1]b
  inner join  [dbo].[Table2] c on c.budgetName = b.productlinename
  inner join [dbo].[Table3] p on p.productline = b.productlinename

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

ALTER TABLE, change collation. That error message indicates your collation usage is inconsistent. — jarlh, Jan 10 '18 at 11:16
Possible duplicate of [Cannot resolve the collation conflict between](https://stackoverflow.com/questions/17616620/cannot-resolve-the-collation-conflict-between) — , Jan 10 '18 at 11:16
where is your `UNION ALL` operator as mentioned in error message ? — Yogesh Sharma, Jan 10 '18 at 11:20
Tables don't have collations; instances, databases, and columns (same as database by default) have collations. One or more of the columns in your actual query have different collations. — Dan Guzman, Jan 10 '18 at 12:23

score 2 · Accepted Answer · answered Jan 10 '18 at 11:25

2

Just add COLLATE DATABASE_DEFAULT in both equal to operations and you are done. Collations can differ, not only on a DB level, but also on a column level. That's probably what's going on here.

answered Jan 10 '18 at 11:25

SQL_M

2,455
2
16
30

score 0 · Answer 2 · answered Jan 10 '18 at 11:26

Add COLLATE to the JOIN syntax like so

  FROM [dbo].[Table1]b
  inner join  [dbo].[Table2] c on c.budgetName COLLATE SQL_Latin1_General_CP1_CI_AS = b.productlinename COLLATE SQL_Latin1_General_CP1_CI_AS 
  inner join [dbo].[Table3] p on p.productline COLLATE SQL_Latin1_General_CP1_CI_AS = b.productlinename COLLATE SQL_Latin1_General_CP1_CI_AS

How resolve table collation mismatch problems in join and union

2 Answers2