How to properly frame a collate command

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to properly frame a collate command

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2011-04-11 : 12:19:00

Hi,

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:

quote:
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?

Cheers,
Matt

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-11 : 12:34:08

Can you show the complete statement?
Have you tried to put the COLLATE ... on the left side of the equal sign?

No, you're never too old to Yak'n'Roll if you're too young to die.

mattt
Posting Yak Master

194 Posts

Posted - 2011-04-12 : 04:28:30

quote:
Originally posted by webfred

Can you show the complete statement?
Have you tried to put the COLLATE ... on the left side of the equal sign?

Nope. Now I'm getting

quote:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

The whole query is massive. I can't really post it all and expect people to filter through it. However, the relevant part of it looks like this:


   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

mattt
Posting Yak Master

194 Posts

Posted - 2011-04-12 : 05:24:26

Found the answer. Here's what you need to do.

In sql server manager to to Server Objects > Linked Servers > find the problematic box.

Right Click > Properties

Under Server Options set "Use Remote Collation" to "False"
and "Collation Name" to "Latin1_General_CI_AS".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources