Cannot resolve the collation conflict

Question

Cannot resolve the collation conflict

hemak

SSC Enthusiast

Points: 185
More actions
March 21, 2022 at 10:46 pm

#4003753

Hello!

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation
I have two tables -
Table1 as linuxdata ( only one column and that is UID - nvarchar(50)
Table2 as coumputerdata (columns: id (uniqueidentifier) , name(nvarchar(63), importid(nvarchar(80) .. and so on)
UID of table1 and importdid of table2 has same data(different columns names)
I want to see UID of table1 with its associated name from importid of table2 in my output.
Here is my query and I get collation error.
SELECT fc.importid, fc.name
FROM dbo.computerdata fc
LEFT JOIN dbo.linuxdata sd ON fc.importid=sd.uid

please help!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18765 More actions · Answer 1

You need to cast the join columns to the same collation. Ideally you should sort out the collation in the database as casting negates the use of indexes.

As you have not posted the table structure the following casts both columns.

SELECT fc.importid, fc.[name]
FROM dbo.computerdata fc
LEFT JOIN dbo.linuxdata sd
ON fc.importid COLLATE SQL_Latin1_General_CP1_CI_AS
= sd.[uid] COLLATE SQL_Latin1_General_CP1_CI_AS;

hemak SSC Enthusiast Points: 185 More actions · Answer 2

Thank you Ken.. collation error is gone. But my output is different than what I'm expecting thou!

UID column in table(linuxdata) and importid column in table(computerdata) has same information , just column name is different.

I want UID column in linuxdata table to map with the importid column in computerdata table and give the output of UID( from linuxdata table ) with its name(column from table2)

please let me if I'm explaining correct!

This reply was modified 2 years ago by hemak.

Ed B Hall of Fame Points: 3615 More actions · Answer 3

hemak wrote:

I want UID column in linuxdata table to map with the importid column in computerdata table and give the output of UID( from linuxdata table ) with its name(column from table2)

The query already gives you the UID and the name, however, as there is a left join I would rearrange the tables. In theory this may return some null values for importid and [name]. If there is no possibility of nulls, an inner join could be used and it would not matter which order the tables are listed. (I assume the computerdata table already has the server default collation so doesn't need to be cast).

SELECT  sd.uid, 
        fc.importid,
        fc.[name]
FROM    dbo.linuxdata AS sd
LEFT    OUTER JOIN dbo.computerdata AS fc ON sd.[uid] COLLATE SQL_Latin1_General_CP1_CI_AS = fc.importid;

This reply was modified 2 years ago by Ed B.
This reply was modified 2 years ago by Ed B. Reason: spelling

hemak SSC Enthusiast Points: 185 More actions · Answer 4

yes, this is the output I was looking for. since importid and UID has same information , I removed fc.importid in the beginning as below.

SELECT sd.[UID],fc.name

FROM dbo.linuxdata sd

LEFT JOIN dbo.computerdata fc

on fc.importid COLLATE SQL_Latin1_General_CP1_CI_AS

= sd.uid COLLATE SQL_Latin1_General_CP1_CI_AS

Thank you very much for your help and support! I just started learning sql and this is already driving me crazy but its fun thou!

This reply was modified 2 years ago by hemak.
This reply was modified 2 years ago by hemak.

hemak SSC Enthusiast Points: 185 More actions · Answer 5

So now i have different requirement that I'm trying to solve , same scenario but little modifications on the requirments.

instead of having table 1 in sql server, Lets assume i have that table outside server in a location and i want to ready that as csv or something using python. How can we compare with the csv file from outside and foundcomputer_table in sql server to get the same output as we discussed above ?