Post reply

Msg 446: Cannot resolve collation conflict for equal to operation.

  • January 23, 2006 at 3:36 am

    #92857

    hi!

    i tried the next:

    select * from TBL1 where COL1+COL2+COL3 not in (select COL from TBL2)

    and i get this message:

    Msg 446: Cannot resolve collation conflict for equal to operation.

    i dont understand why? before this i can use this script in another DB. is there any setting which can block to run this query?

    thx G

  • January 23, 2006 at 3:51 am

    #616358

    May be the collation settings for the two tables are not same....

    ..hema

  • January 23, 2006 at 4:13 am

    #616365

    yes! you're right! i didnt check this..

    can u tell me where can i change the collation of the DB?

    thx G

  • January 23, 2006 at 4:38 am

    #616374

    ALTER DATABASE MyDatabase COLLATE French_CI_AS

    Note that this won't change the collation of existing tables. it will affect the following (from Books online)

    • The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.
    • Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables to the new collation.
    • All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions to the new collation.
    • The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, to the new default collation.

    To change the colation of an existing column in a table, you need to do soemthing like this

    ALTER TABLE MyTable ALTER COLUMN CharCol

                varchar(10)COLLATE Latin1_General_CI_AS NOT NULL

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 23, 2006 at 4:41 am

    #616378

    You can use :

    'sp_dboption' stored procedure to change the collation setting for the database..

    ..hema 

  • January 23, 2006 at 4:50 am

    #616381

    From books online:

    sp_dboption is supported for backward compatibility. Use ALTER DATABASE to set database options.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 23, 2006 at 4:56 am

    #616383

    thanks

    another question: i changed the collation of my database but the colums collation hasn't changed.

    is there any solution to change all columns' collation in one step?

    thx G

  • January 23, 2006 at 4:59 am

    #616385

    can i update the SYSCOLUMNS table?

    >> I checked. unfortunately no.

    so, i have any idea right now.

  • January 23, 2006 at 5:03 am

    #616388

    Not unless you want to risk a corrupt database.

    See my first post in this thread. I noted exactly what changing the db collation changes and how to change the collation of columns.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 23, 2006 at 6:10 am

    #616402

    Update the statistics of the database and see...

    I am not sure about this..but i hope it may work..

    ..hema

  • January 23, 2006 at 6:16 am

    #616403

    Huh?

    Statistics is a measure of the distribution of values within a column. It has nothing to do with collation.

    If you want to change the collation of existing columns in existing tables you must use ALTER TABLE <tablename> ALTER COLUMN <columnname> <datatype> COLLATE <new desired collation>

    Note the following:

    You cannot alter the collation of a column that is currently referenced by:

    • A computed column.
    • An index.
    • Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
    • A CHECK constraint.
    • A FOREIGN KEY constraint.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 24, 2006 at 3:06 am

    #616606

    To change collation on existing tables you neeed to run ALTER TABLE statements like GilaMonster wrote.

    You can generate these up from sysobjects and syscolumns.

    Alternativeley you can alter collation on the fly in your query. Something like...

    not in (select COL COLLATE Latin1_General_CS_AI from TBL2)

     

     

  • January 24, 2006 at 3:07 am

    #616608

    If you chnage the collation there is a risk it will have a knock on effect on other queries that were prevously working. I usually the cast the join expression, not quite sure how to do this in a NOT IN, but in a simple join such as..

    select * from t1 inner join t1 on t1.cola = t2.cola

    it would become

    select * from t1 inner join t1 on t1.cola = t2.cola collate database_default

     


    Thanks Jeet

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply