Solved

collation conflict

Posted on 2010-08-31
8
414 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
  • 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
 
LVL 1

Author Comment

by:Wildone63
ID: 33569345
How can I change the Latin1 to SQL_Latin1
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now