| Author |
 Topic  |
|
|
edfollett
Starting Member
Egypt
1 Posts |
 Posted - 11/02/2004 : 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 - 11/02/2004 : 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
United Kingdom
22191 Posts |
Posted - 11/02/2004 : 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
United Kingdom
22191 Posts |
Posted - 11/02/2004 : 09:12:36
|
X002548's "Restored Data" suggestion would account for "imported data" ... I didn't think of that
Kristen |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 11/02/2004 : 09:23:34
|
select * from profile, userinfo
where profile.custid = userinfo.custid collate database_default
pink for painful
rockmoose |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/03/2004 : 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
Sweden
3279 Posts |
Posted - 11/03/2004 : 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
Sweden
3279 Posts |
Posted - 11/03/2004 : 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
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 11/03/2004 : 03:51:53
|
hmm.. probably different case with ours, i just recreated the tables 
--------------------
keeping it simple... |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 11/03/2004 : 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
United Kingdom
22191 Posts |
Posted - 11/03/2004 : 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
Sweden
3279 Posts |
Posted - 11/03/2004 : 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
United Kingdom
284 Posts |
Posted - 02/04/2006 : 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.... |
Edited by - hog on 02/04/2006 18:14:12 |
|
|
|
hog
Constraint Violating Yak Guru
United Kingdom
284 Posts |
Posted - 02/04/2006 : 19:21:50
|
And the result is SUCCESS :)
Thanks dudes, another successful fix :) |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 02/05/2006 : 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
United Kingdom
284 Posts |
Posted - 02/06/2006 : 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 :) |
|
|
| |
Topic |
|
Collation COnflict!!
Forum Locked
