Solved

collation conflict

Posted on 2010-08-31
8
418 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:Wildone63
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 6

Expert Comment

by:anushahanna
ID: 33569286
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
 
LVL 1

Author Comment

by:Wildone63
ID: 33569318
They come back blank for the collation_name
0
 
LVL 1

Author Comment

by:Wildone63
ID: 33569339
Oops wrong server.
They came back different one is SQL_Latin and the other is Latin
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Wildone63
ID: 33569345
How can I change the Latin1 to SQL_Latin1
0
 
LVL 12

Accepted Solution

by:
GMGenius earned 250 total points
ID: 33569355
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
 
LVL 6

Assisted Solution

by:anushahanna
anushahanna earned 250 total points
ID: 33569490
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
 
LVL 6

Expert Comment

by:anushahanna
ID: 33569499
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
 
LVL 1

Author Closing Comment

by:Wildone63
ID: 33569737
Thank You.
The query from GMGenius did work, and the Alter Table works like a charm.

Thank You Both.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question