SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Collation COnflict!!
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

edfollett
Starting Member

Egypt
1 Posts

Posted - 11/02/2004 :  09:06:20  Show Profile
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 - 11/02/2004 :  09:09:09  Show Profile
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-)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/02/2004 :  09:09:25  Show Profile
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/02/2004 :  09:12:36  Show Profile
X002548's "Restored Data" suggestion would account for "imported data" ... I didn't think of that

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 11/02/2004 :  09:23:34  Show Profile
select * from profile, userinfo
where profile.custid = userinfo.custid collate database_default

pink for painful

rockmoose
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/03/2004 :  01:03:12  Show Profile
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 11/03/2004 :  03:47:11  Show Profile
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 11/03/2004 :  03:48:38  Show Profile
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
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 11/03/2004 :  03:51:53  Show Profile  Send jen a Yahoo! Message
hmm.. probably different case with ours, i just recreated the tables

--------------------
keeping it simple...
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 11/03/2004 :  03:54:17  Show Profile
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/03/2004 :  13:27:52  Show Profile
"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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 11/03/2004 :  13:46:42  Show Profile
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
Go to Top of Page

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 02/04/2006 :  17:49:24  Show Profile
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....

Edited by - hog on 02/04/2006 18:14:12
Go to Top of Page

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 02/04/2006 :  19:21:50  Show Profile
And the result is SUCCESS :)

Thanks dudes, another successful fix :)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/05/2006 :  13:51:39  Show Profile
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
Go to Top of Page

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 02/06/2006 :  09:47:22  Show Profile
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 :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000