Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Cannot resolve collation conflict for equal to operation.

    Anyone bang into that?

    RESTORED a 7.0 Datbase CP 52 to a 2k box

    Built a "Privacy" db from scratch on 2k box

    Looked at some table scripts and got

    Code:
    OHM - sql 7
    
    SQL_Latin1_General_CP1_CI_AS NULL 
    
    Privacy - sql 2k
    
    Latin1_General_CI_AS NULL
    
    [
    Ran this and got the error...any help?

    Code:
    select * from privacy..privacy_column p inner join ohm.information_schema.tables t ON p.table_name = t.table_name
    Server: Msg 446, Level 16, State 9, Line 1
    Cannot resolve collation conflict for equal to operation.

    Any ideas?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You need to use the collate keyword in the query. Going to be a pain in the neck, too. You will end up with

    Code:
    select * 
    from privacy..privacy_column p inner join 
    ohm.information_schema.tables t ON p.table_name = t.table_name collate SQL_Latin1_General_CP1_CI_AS
    Because of this, I am debating whether to ban any collate statements on table creation scripts here. Just use the default. Please!

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thanks, I think I got that....and the collation join and all

    what a mess

    But what bugs me is the 7.0 restore...seems to be CP 52 and the columns are collation

    SQL_Latin1_General_CP1_CI_AS NULL

    And the Northwind and the newly built scrambling DB are

    Latin1_General_CI_AS NULL

    So unless I forget, you can't restore a db to a server instance with a different CP...but it seems like 7's collation doesn't translate in to the standard 2k collation....

    Is this correct?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Correct. The SQL collations and the Windoze collations are considered distinct even if they have the same inflection options (case and accent sensitivity).

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn....means I have to be platform specific...

    What was that movie? 1993

    "I hate being right all the time" *


    * disclaimer: You're kidding, right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, the distinction between SQL collations and Windoze collations are the same everywhere. SQL_Latin_General_BIN and Latin_General_BIN are ALWAYS different collations.

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Pat,

    Both of those collations came from a DDL script out of EM. You're losing me now.

    One was from a restored 7.0 database on a 2k box:

    SQL_Latin1_General_CP1_CI_AS NULL

    And the others are from a sql server 2k built db

    Latin1_General_CI_AS NULL

    Are you saying the RESTORED 7.0 DB inhereted the windoze collation? Not the servers default collation?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I can not be sure, since it has been a while since I have played with SQL7, but I think someone who installed the SQL 2K box, may have played with the default settings a little. The default for an install of sql2K is SQL_latin_general_CP1_CI_AS. I forget what the default was for SQL 7, but I expect it was the same.

    EDIT: Oh, and a database retains its collation, when restored. At least, when you go from SQL 2K to SQL 2K. What was the collation of the database on the SQL 7 server?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by MCrowley
    EDIT: Oh, and a database retains its collation, when restored. At least, when you go from SQL 2K to SQL 2K. What was the collation of the database on the SQL 7 server?
    That's exactly what I think happened, and yes Brett, those collations are different.

    -PatP

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dig this...

    Dev CP 53...so Ican't restore Prod CP 52 to dev..iditots...

    ok so I get by CP...and restore to 2k....

    Now I have a sql 2k box set up in Ireland with a standard CP, but a non standard collation?


    This seems to be the out of the box collation

    SQL_Latin1_General_CP1_CI_AS NULL

    right? At least that's what I have...

    To many cooks spoil the environment
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think you need to have somebody go to Ireland and help straighten things up! I'll go, I'll go!!!

    -PatP

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Brett: Here is a thread that could help. Or at least commiserate.

    http://www.dbforums.com/t1000827.html

Posting Permissions

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