Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" .

Question

Post reply

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" .

blnbmv

SSC Eights!

Points: 900
More actions
August 8, 2008 at 7:30 am

#190888

Hi members,
I am getting the following error:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
when I am trying to select table which is in some other database but with in the same server.
The cursor is as follows:
DECLARE Cur_Search CURSOR FOR
SELECT
GId,
ParentId,
EntityId
FROM
EntityCache.dbo.TblTree T1 inner join ProtectList T2
ON T2.Global_ID=T1.Global_GID
and T2.GLobal_PID=T1.Global_PID and T2.Global_Entity_ID = T1.Global_entity_id
Where
t1.change<>'Y'
please let me know why collation error comes. It is very urgent
thanks in advance.

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

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Use "COLLATE Latin1_General_CI_AS" in places where you do things with strings of different collations. Like:

T2.Global_ID=T1.Global_GID COLLATE Latin1_General_CI_AS

This si the quickfix. To do it properly you should look at why you are using obsolete collations (the one with SQL_ prefix)

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

r.dragoi Right there with Babe Points: 788 More actions · Answer 2

There are some examples here http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/ ..

Cheers,

R

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

Most likely case is that the twqo databases have different default collations. Even though they are on the same server it's possible to have different default collations for each database.

Anothwer possiblity is that the tables have been created with different collations.

As a workaround you can use the COLLATE clause.

Something like this:

DECLARE Cur_Search CURSOR FOR

SELECT

GId,

ParentId,

EntityId

FROM

EntityCache.dbo.TblTree T1 inner join ProtectList T2

ON T2.Global_ID=T1.Global_GID

and T2.GLobal_PID=T1.Global_PID and T2.Global_Entity_ID = T1.Global_entity_id

Where

CONVERT(varchar(50),t1.change COLLATE SQL_Latin1_General_CP1_CI_AS)<>'Y'

This is probably not the exact solution for your problem, but it should help you on the way.

[font="Verdana"]Markus Bohse[/font]

blnbmv SSC Eights! Points: 900 More actions · Answer 4

Hi members,

I thank each and every Member for giving support in solving my issue and also I am proud about moderators and also Mentors of this group as there doing great job.

Thanks,

Sainath