Solved

collation conflict

Posted on 2010-08-31
8
415 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

773 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