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

Old Forums

CLOSED - General SQL Server

Collation COnflict!!

Author

Topic

edfollett
Starting Member

1 Post

Posted - 2004-11-02 : 09:06:20

hi,

PLEASE HELP!

we are trying to resolve this error with our database:

we are trying simply to join these two tables: with this sql!

select * from profile, userinfo
where profile.custid = userinfo.custid
;

the error message that we recieve =

cannot resolve collation conflict for equal to operation

we have never come across collation conflicts before so please advise.

PLEASE HELP!

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-02 : 09:09:09

You restored a database to a server that has a different collation

You need to make thew collation part of the join syntax....very painful...

I rebuilt a server because of this....

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-11-02 : 09:09:25

Your PROFILE and USERINFO tables have been created with columns using different collations. Or perhaps some data has been imported which has a different collation (don't know if you can do that, but changing the collation leaves the existing data in its original collation)

You can force the collation used in the comparison, but I expect it would be better to sort it out so that the columns (AND the existing data that is in them) have the same collation sequence.

Kristen

Kristen
Test

22859 Posts

Posted - 2004-11-02 : 09:12:36

X002548's "Restored Data" suggestion would account for "imported data" ... I didn't think of that

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-02 : 09:23:34

[code]select * from profile, userinfo
where profile.custid = userinfo.custid collate database_default[/code]
pink for painful

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-11-03 : 01:03:12

Probably need to force collation on both sides, don't you?

select * from profile, userinfo
where profile.custid collate database_default = userinfo.custid collate database_default

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-03 : 03:47:11

quote:
Originally posted by Kristen

Probably need to force collation on both sides, don't you?

select * from profile, userinfo
where profile.custid collate database_default = userinfo.custid collate database_default

Kristen

I had to double check, and this makes me a bit confused.
( I have always prior to this just specified 1 collation clause )

It seems that if you specify 1 collation clause then it applies to the whole comparison.
What's your conclusion Kristen ?


SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation')

create table t1(c varchar(35) collate Albanian_CI_AI)
create table t2(c varchar(35) collate database_default)

insert t1 select 'text'
insert t2 select 'text'

select * from t1 join t2 on t1.c = t2.c collate Albanian_CI_AI
select * from t1 join t2 on t1.c = t2.c collate database_default

select * from t1 join t2 on t1.c collate Albanian_CI_AI = t2.c
select * from t1 join t2 on t1.c collate database_default = t2.c

select * from t1 join t2 on t1.c collate Albanian_CI_AI = t2.c collate database_default
select * from t1 join t2 on t1.c collate database_default = t2.c collate Albanian_CI_AI

drop table t1
drop table t2
-- select * from ::fn_helpcollations()

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-03 : 03:48:38

quote:
Originally posted by X002548

You need to make thew collation part of the join syntax....very painful...

I rebuilt a server because of this....

Here too

rockmoose

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-03 : 03:51:53

hmm.. probably different case with ours, i just recreated the tables

--------------------
keeping it simple...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-03 : 03:54:17

quote:
Originally posted by jen

hmm.. probably different case with ours, i just recreated the tables

Yeah, IOC we want all the servers to run with the same server collation.

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-11-03 : 13:27:52

"It seems that if you specify 1 collation clause then it applies to the whole comparison.
What's your conclusion Kristen ?"

Never knew that.

I thought you were cheating by using a collation that was either one or other column, but

select * from t1 join t2 on t1.c = t2.c collate Estonian_CS_AS

works just as well! and specifically both these fail:

select * from t1 join t2 on t1.c= t2.c
select * from t1 join t2 on t1.c collate Latin1_General_BIN = t2.c collate Estonian_CS_AS

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-03 : 13:46:42

Well, the collate statements I have done, just have worked.
And i always thought it was a bit weird that it didn't matter if you did:

col1 = col2 collate ...
or
col2 = col1 collate ...

One of those things that just work :)

rockmoose

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-04 : 17:49:24

Hi,

I have read this thread but am still completely confusticated :(

I used the generate create sql option in QA to create a script to create a table, a snippet below:

CREATE TABLE [mytable] (
[timestamp] [binary] (8) NULL ,
[customer_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ship_to_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address_name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[short_name] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

The collation entry still confuses me and I don't know where it is coming from?

I am able to import data into the table using enterprise manager without any errors, but when I try to duplicate procedures from the other server on my local one I get the collation error.

I don't understand how I can make the import data match the collation or whether I can modify the create script to omit the collation entries without stuffing everything up?

Any advise would be very welcome.

Thx

PS just ready more posts I see the collation is set on install. I just ran the code to get the collation on the host and my local copy.

host = SQL_Latin1_General_CP1_CI_AS

local_host = Latin1_General_CI_AS

SO I am going to uninstall MSDE and reinstall and see if I see a setting to select the matching collation....

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-04 : 19:21:50

And the result is SUCCESS :)

Thanks dudes, another successful fix :)

Kristen
Test

22859 Posts

Posted - 2006-02-05 : 13:51:39

I recommend that you always explicitly put the COLLATE statement in any Create Table statement (including explicitly creating an #TempTable or @TableVar and using COLLATE). That way you won't get surprises on a server which has a different collation in TEMPDB, or the Server in general.

Also include COLLATE directives on Assignments and WHERE clauses (string columns only) where the tables come from MULTIPLE databases.

Kristen

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-02-06 : 09:47:22

Hi, the create table state did have the collation clause as generated by the parent host but my local copy of MSDE had a different setting which I was unaware of until this post :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources