collation conflict between "SQL_Latin1_General_CP1_CI_AS"

Question

collation conflict between "SQL_Latin1_General_CP1_CI_AS"

sc-w

Hall of Fame

Points: 3667
More actions
November 24, 2009 at 4:37 am

#137093

Hi,
I am trying to run the following query but keep getting this error below, i have tried various things to correct the error but i can't seem to get it right.
If anyone can point me in the right direction i would really appreciate it.
Error:
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation
Code:
with cteEmployers( EDRS, Employer_Name, Emp_Phone, Emp_Address_1, Emp_Address_2, Emp_Address_3, Emp_Address_4, Emp_Address_Pcode1, Emp_Address_Pcode2, rown) as(Select wce_ilr.EDRS, wce_ilr.Employer_Name, wce_ilr.Emp_Phone, wce_ilr.Emp_Address_1, wce_ilr.Emp_Address_2, wce_ilr.Emp_Address_3, wce_ilr.Emp_Address_4, wce_ilr.Emp_Address_Pcode1, wce_ilr.Emp_Address_Pcode2, ROW_NUMBER() OVER (PARTITION BY wce_ilr.edrs ORDER BY wce_ilr.edrs) from wce_ilr left join wce_contact
on WCE_ILR.edrs COLLATE database_default =wce_contact.edrs COLLATE database_default
where WCE_contact.edrs is null)
SELECT *
from wce_contact
where edrs in (
select edrs
from cteEmployers
)

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

There's a comparison in the "IN" clause, so you have to define explicitly the collation you are using both in the subquery select list and outer query where clause.

Try this:

WITH cteEmployers (

EDRS,

Employer_Name,

Emp_Phone,

Emp_Address_1,

Emp_Address_2,

Emp_Address_3,

Emp_Address_4,

Emp_Address_Pcode1,

Emp_Address_Pcode2,

rown

)

AS (

SELECT wce_ilr.EDRS COLLATE database_default,

wce_ilr.Employer_Name,

wce_ilr.Emp_Phone,

wce_ilr.Emp_Address_1,

wce_ilr.Emp_Address_2,

wce_ilr.Emp_Address_3,

wce_ilr.Emp_Address_4,

wce_ilr.Emp_Address_Pcode1,

wce_ilr.Emp_Address_Pcode2,

ROW_NUMBER() OVER ( PARTITION BY wce_ilr.edrs ORDER BY wce_ilr.edrs )

FROM wce_ilr

LEFT JOIN wce_contact

ON WCE_ILR.edrs COLLATE database_default = wce_contact.edrs COLLATE database_default

WHERE WCE_contact.edrs IS NULL

)

SELECT *

FROM wce_contact

WHERE edrs COLLATE database_default IN (

SELECT edrs

FROM cteEmployers

)

Hope this helps

Gianluca

-- Gianluca Sartori

sc-w Hall of Fame Points: 3667 More actions · Answer 2

Thanks that worked! However with this query working raises another question to an issue I have. Not sure if I should put up a new post for this or not but here it goes.

The query below selects data from one table and where it doesn't find an edrs / uniqueid that already exists in the wce_contact table it inserts the results. This same query works for another system fine just with a slightly different field structure.

The problem this query is giving me is:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'pk_wce_contact_uid'. Cannot insert duplicate key in object 'dbo.wce_contact'.

The statement has been terminated.

with cteEmployers( EDRS, Employer_Name, Emp_Phone, Emp_Address_1, Emp_Address_2, Emp_Address_3, Emp_Address_4, Emp_Address_Pcode1, Emp_Address_Pcode2, rown) as(Select wce_ilr.EDRS, wce_ilr.Employer_Name, wce_ilr.Emp_Phone, wce_ilr.Emp_Address_1, wce_ilr.Emp_Address_2, wce_ilr.Emp_Address_3, wce_ilr.Emp_Address_4, wce_ilr.Emp_Address_Pcode1, wce_ilr.Emp_Address_Pcode2, ROW_NUMBER() OVER (PARTITION BY wce_ilr.edrs ORDER BY wce_ilr.edrs) from wce_ilr left join wce_contact

on WCE_ILR.edrs =wce_contact.edrs COLLATE database_default

where WCE_contact.edrs is null)

INSERT INTO wce_contact (UNIQUEID, EDRS, COMPANY, PHONE, ADDRESS1, ADDRESS2, CITY, COUNTY, POSTALCODE, Record_Type)

Select edrs, edrs, Employer_Name, Emp_Phone, Emp_Address_1, Emp_Address_2, Emp_Address_3, Emp_Address_4, Emp_Address_Pcode1 + ' ' + Emp_Address_Pcode2 AS Postcode, 'Company' AS Record_Type from cteEmployers where (rown = 1 and not edrs is null) order by cteEmployers.edrs

Now this is crazy as the query identifies that the records that are to be inserted do not exist therefore there can't be a duplicate uniqueid. That is further proved by what i have just had help with as this query comes back with no results.