?
Solved

collation conflict

Posted on 2010-08-31
8
Medium Priority
?
421 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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 1000 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 1000 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

Industry Leaders: 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!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

800 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