collation conflict - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

collation conflict

Author

Topic

Saeed
Starting Member

39 Posts

Posted - 2002-12-01 : 17:02:23

DELETE RollInventory with (rowlock) from RollInventory r , txtRecReversals rr WHERE r.RollN = rr.RollN AND r.RollIDN = rr.RollIDN

===================

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

In RollInventory
=================
ROLLN varchar(18)
RollIDN int 4

In txtRecReversals
=================
RollN varchar(50)
RollIDN int 4

Can anyone tell me what this collation conflict is and how I can
avoid this crash in my spr?

Thanks

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-01 : 17:54:54

I'm no expert on this but BOL says

quote:

Microsoft® SQL Server™ 2000 supports several collations. A collation encodes the rules governing the proper use of characters for either a language, such as Macedonian or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages).

Each SQL Server collation specifies three properties:

The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.

The sort order to use for non-Unicode character data types (char, varchar, and text).

The code page used to store non-Unicode character data.

Seems like somehwere you have a collation conflict, and so SQL Server doesn't know how to do the "=" function. (eg do oranges = apples?)

You can cast values to a common collation before finding out whether they're equal

Delete RollInventory with (rowlock)
from RollInventory r, txtRecReversals rr
where cast(r.RollN as varchar(18)) collate SQL_Latin1_General_CP1_CI_AI
= cast(rr.RolllN as varchar(18)) collate SQL_Latin1_General_CP1_CI_AI

etc

Of course - ideally you should make the collation of both objects the same, and then you won't have to do this.

I really hope that helps,

Cheers

PS You might want to also look up "changing collation" in BOL

CREATE TABLE MyTable
(PrimaryKey int PRIMARY KEY,
CharCol varchar(10) COLLATE French_CI_AS NOT NULL
)
GO
ALTER TABLE MyTable ALTER COLUMN CharCol
varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
GO

C'mon Aussie!
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 12/01/2002 18:01:05

Saeed
Starting Member

39 Posts

Posted - 2002-12-01 : 18:12:09

Thanks mate

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-01 : 18:21:35

pleasure...you got me checking out BOL for better explanations - check out "code page, described" in BOL for a full description if you like

Did you solve your problem?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources