I'm trying to write a query across two linked servers. Having set up the link with an appropriate logon, I'm getting this collate error:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
This is new to me, but it looks like a charset conflict, so I googled it and discovered the collate command which can be added to my join. However I've tried both
va ON V_ID1 = c1.strData COLLATE Latin1_General_CI_AS
and
va ON V_ID1 = c1.strData COLLATE SQL_Latin1_General_CP1_CI_AS
And neither seems to resolve the error. What am I doing wrong?
EDIT: The entire query is too big to post and expect people to filter through, but here's the relevant bit:
select
rel.intParentAttributeId as intItemId,
case va.type
when 'SM' then 120000000
when 'FB' then 110000000
when 'RO' then 100000000
when 'SP' then 90000000
when 'A' then 80000000
when 'D' then 70000000
when 'P' then 60000000
when 'SR' then 50000000
when 'FN' then 40000000
when 'RL' then 30000000
when 'VO' then 20000000
when 'RE' then 10000000
end
+
rel.intParentAttributeId
as fltStrength
from
AttributeMap ky
join Catalogue c1 on c1.intRowId=ky.intChildAttributeId and c1.intAttributeTypeId=@intsimilarAtt
join
[SQLSERVERNAME].[SchemaName].dbo.VidAssc
va on V_ID1 COLLATE SQL_Latin1_General_CP1_CI_AS = c1.strData COLLATE SQL_Latin1_General_CP1_CI_AS
join Catalogue c2 on c2.strData=V_ID2 and c2.intAttributeTypeId=@intsimilarAtt
join AttributeMap rel on rel.intChildAttributeId = c2.intRowId
join @tmpSeeds s on s.intItemId = ky.intParentAttributeId
group by rel.intParentAttributeId,va.Type
)sqi
Cheers, Matt
OK, finally found a fix. Here's what you have to do:
In sql server manager to to Server Objects > Linked Servers > and find the problematic linked server.
Right Click > Properties
Under Server Options set "Use Remote Collation" to "False" and "Collation Name" to "Latin1_General_CI_AS"