collation conflict

I am using SQL 2005, I have a database with 4 tables.

One table is nsicitem, and one table is ol
When I run this query
select
ol.item,
ol.Description,
ol.NEW_ITEM,
ol.NEW_DESCRIPTION,
nsicitem.item,
nsicitem.itmdesc
from ol
left join nsicitem on nsicitem.item = ol.item

I get an error

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

I checked both tables and they are botl SQL_Latin1_General_CP1_CI_AS

Can some one help me with this please.

Thank You.
LVL 1
Wildone63Asked:
Who is Participating?
 
GMGeniusConnect With a Mentor Commented:
Hi Wildone63
If you are unsure about collation you could try changing your query to imply the correct collation for the join
eg
select
ol.item,
ol.Description,
ol.NEW_ITEM,
ol.NEW_DESCRIPTION,
nsicitem.item,
nsicitem.itmdesc
from ol
left join nsicitem on nsicitem.item = ol.item
will become
select
ol.item,
ol.Description,
ol.NEW_ITEM,
ol.NEW_DESCRIPTION,
nsicitem.item,
nsicitem.itmdesc
from ol
left join nsicitem on nsicitem.item
COLLATE SQL_Latin1_General_CP1_CI_AS = ol.item
COLLATE SQL_Latin1_General_CP1_CI_AS
This has worked for me many times in the past.
0
 
anushahannaCommented:
The collation is at a column-level (for text fields)

Can you check
select COLLATION_NAME from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ol'
and COLUMN_NAME = 'item'

select COLLATION_NAME from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'nsicitem'
and COLUMN_NAME = 'item'
0
 
Wildone63Author Commented:
They come back blank for the collation_name
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Wildone63Author Commented:
Oops wrong server.
They came back different one is SQL_Latin and the other is Latin
0
 
Wildone63Author Commented:
How can I change the Latin1 to SQL_Latin1
0
 
anushahannaConnect With a Mentor Commented:
example:

ALTER TABLE nsicitem ALTER COLUMN item
varchar(10) --verify the data type
COLLATE
Latin1_General_CI_AS --put the collation you want
NOT NULL -- mention nullability
0
 
anushahannaCommented:
GMGenius's idea will work fine for your query.

but if you want to change the collation at the schema level, then you need to use the ALTER statement.
0
 
Wildone63Author Commented:
Thank You.
The query from GMGenius did work, and the Alter Table works like a charm.

Thank You Both.
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.

All Courses

From novice to tech pro — start learning today.