Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Posts
    21

    Unanswered: Cannot resolve collation conflict for equal operation

    Hi, Im having a critical problem.

    I have two databases: persons and cars

    In database persons I have a table
    named persons_class1 as follows:
    person_id char(13) not null [primary key]
    name varchar(20) not null

    In database cars I have a table
    named cars_blue as follows:
    car_id char(13) not null [primary key]
    model varchar(20) not null
    person_id char(13) not null

    In my program the initial catalog is: cars
    and Im executing the following SQL:

    select cars_blue.model,persons.dbo.persons_class1.name
    from cars_blue,persons.dbo.persons_class1
    where cars_blue.person_id=persons.dbo.persons_class1.per son_id

    and the following error occurs:
    Cannot resolve collation conflict for equal operation

    I also tried like instead of = and the error is:
    Cannot resolve collation conflict for like operation

    what can I do?

    Thanks in advance
    Roland

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A collation is the way that characters are compared/sorted. Apparently your personid columns are character based, so at least at first I'd suggest using:
    Code:
    select cars_blue.model,persons.dbo.persons_class1.name
    from cars_blue,persons.dbo.persons_class1
    where cars_blue.person_id COLLATE SQL_Latin1_General = persons.dbo.persons_class1.per son_id COLLATE SQL_Latin1_General
    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Collation does not pertain to the fields that are being compared but to the operator that is used in comparison.

    select cars_blue.model,persons.dbo.persons_class1.name
    from cars_blue,persons.dbo.persons_class1
    where cars_blue.person_id = persons.dbo.persons_class1.per son_id COLLATE SQL_Latin1_General_CP1251_CI_AS
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    Collation does not pertain to the fields that are being compared but to the operator that is used in comparison.
    So you apply the collation to the operator, and once the collation is applied that operator will then compare any two objects? At least as I see it, the comparison operator invokes an operation which is dependant on the collations of both of the objects that are being compared. Did I miss a meeting somewhere?

    -PatP

  5. #5
    Join Date
    Jun 2004
    Posts
    21
    Great, I solved the problem!!, further Im allowed to change
    the default collation of the database.

    Thanks!
    Roland

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, he already solved it, so there is no point to continue, but...The COLLATE tells the optimizer what code page to use while comparing 2 values, not how they need to be collated before comparison can be performed. That's why your syntax will produce an error.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So then how can this be?
    Code:
    SELECT Count(*)
       FROM dbo.sysobjects AS a
       WHERE  a.name = a.name
    GO
    
    SELECT Count(*)
       FROM dbo.sysobjects AS a
       WHERE  a.name COLLATE Latin1_General_BIN = a.name 
    
    SELECT Count(*)
       FROM dbo.sysobjects AS a
       WHERE  a.name COLLATE Latin1_General_BIN = a.name COLLATE Latin1_General_BIN 
    GO
    
    SELECT Count(*)
       FROM dbo.sysobjects AS a
       WHERE  a.name COLLATE Latin1_General_BIN = a.name COLLATE Cyrillic_General_BIN 
    GO
    -PatP

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh for Codd's sake, as you see yourself, the fourth statement will produce an error because you can't perform a comparison after collating 2 values to different code pages. Doesn't it mean to you that only 1 COLLATE statement actually makes sense? In the example below you'll see that regardless of what collation is chosen for a field, COLLATE forces the comparison to be performed using a specified code page. It does not "collate" the values!

    begin tran
    go
    create table #t1 (f1 char(1) collate Cyrillic_General_BIN not null)
    create table #t2 (f1 char(1) collate latin1_general_bin not null)
    insert #t1 values ('a')
    insert #t2 values ('a')
    go
    select * from #t1, #t2 where #t1.f1 = #t2.f1 collate French_BIN
    select * from #t1 inner join #t2 on #t1.f1 = #t2.f1 collate French_BIN
    go
    rollback tran
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    Oh for Codd's sake, as you see yourself, the fourth statement will produce an error because you can't perform a comparison after collating 2 values to different code pages. Doesn't it mean to you that only 1 COLLATE statement actually makes sense? In the example below you'll see that regardless of what collation is chosen for a field, COLLATE forces the comparison to be performed using a specified code page. It does not "collate" the values!
    In your earlier post I thought you said that the collation only applied to the operator, which I assumed meant there could only be one collation for an operator. What operator are you collating within your CREATE TABLE statements?

    A collation applies to data, and within SQL Server it specifically applies to character data. You can apply operators to that character data, but you can't apply a collation to the operators. The relational operators (like equal, greather than, etc) use the collation to determine how they can compare the data. This determines things like does case matter, are accents important, what order should the characters sort, etc. Note that one set of rules can be used for sorting and a quite different set can be used for comparisons in some cases within the same collation!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •