• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 68
  • Last Modified:

SQl Server 2008 R2 Collation Conflict when joining two tables

Good morning experts! As I fumble my way around sql I have what I think is a very simple join between a table and a view. Below is my sql statement but when I attempt to run I receive the following error:

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

I have zero clue on how to resolve. What am I missing?


select dbo.MasterCostCodeList.Level1, dbo.MasterCostCodeList.Level2, dbo.MasterCostCodeList.Level3, dbo.MasterCostCodeList.Description, dbo.MasterCostCodeList.Active, dbo.CPtoTCP_ImportB.Project,
dbo.CPtoTCP_ImportB.[2nd Level], CPtoTCP_ImportB.[3rd Level], CPtoTCP_ImportB.[Desc], CPtoTCP_ImportB.ORG_ID, CPtoTCP_ImportB.Expr1
From dbo.MasterCostCodeList
inner join dbo.CPtoTCP_ImportB on dbo.MasterCostCodeList.Level3=dbo.CPtoTCP_ImportB.[3rd Level]
where dbo.MasterCostCodeList.Active = '1'
and CPtoTCP_ImportB.Expr1 = 'Y'
0
DJ P
Asked:
DJ P
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Please use this ..

We need to change the collation on the fly as their collations are different..

COLLATE SQL_Latin1_General_CP1_CI_AS

select dbo.MasterCostCodeList.Level1, dbo.MasterCostCodeList.Level2, dbo.MasterCostCodeList.Level3, dbo.MasterCostCodeList.Description, dbo.MasterCostCodeList.Active, dbo.CPtoTCP_ImportB.Project,
dbo.CPtoTCP_ImportB.[2nd Level], CPtoTCP_ImportB.[3rd Level], CPtoTCP_ImportB.[Desc], CPtoTCP_ImportB.ORG_ID, CPtoTCP_ImportB.Expr1
From dbo.MasterCostCodeList
inner join dbo.CPtoTCP_ImportB on dbo.MasterCostCodeList.Level3 COLLATE SQL_Latin1_General_CP1_CI_AS =dbo.CPtoTCP_ImportB.[3rd Level] COLLATE SQL_Latin1_General_CP1_CI_AS
where dbo.MasterCostCodeList.Active = '1'
and CPtoTCP_ImportB.Expr1 = 'Y'

Open in new window


OR

COLLATE SQL_Latin1_General_CP850_BIN


select dbo.MasterCostCodeList.Level1, dbo.MasterCostCodeList.Level2, dbo.MasterCostCodeList.Level3, dbo.MasterCostCodeList.Description, dbo.MasterCostCodeList.Active, dbo.CPtoTCP_ImportB.Project,
dbo.CPtoTCP_ImportB.[2nd Level], CPtoTCP_ImportB.[3rd Level], CPtoTCP_ImportB.[Desc], CPtoTCP_ImportB.ORG_ID, CPtoTCP_ImportB.Expr1
From dbo.MasterCostCodeList
inner join dbo.CPtoTCP_ImportB on dbo.MasterCostCodeList.Level3 COLLATE SQL_Latin1_General_CP850_BIN =dbo.CPtoTCP_ImportB.[3rd Level] COLLATE SQL_Latin1_General_CP850_BIN
where dbo.MasterCostCodeList.Active = '1'
and CPtoTCP_ImportB.Expr1 = 'Y'

Open in new window

1
 
DJ PAuthor Commented:
Awesome! Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now