Post reply

changing DB collation doesn''t work

  • September 8, 2006 at 1:04 pm

    #60768

    I run a small test that changed the DB collation with the alter DB command. After changing the collation the new collation is shown in the statement and in the EM. The problem is that it doesn't reflect the sorting - the order by doesn't order the name according to the collation od the database in the moment.

    Is there a workaround? What can I do to get the right sort for the collation specified in the alter databa command?

    Thanks a lot,

    mj

    Test:

    select convert (sysname, DatabasePropertyEx(db_name(),'Collation'))

    SQL_Latin1_General_CP1_CI_AS

    select * from test_data

    Óskar

    Oskar

    Patric

    óskar

    oskar

    Mary

    Ðtest

    Dtest

    ðtest

    Etest

    etest

    Ztest

    Ötest

    Alter database latin1 collate Icelandic_CI_AS

    select convert (sysname, DatabasePropertyEx(db_name(),'Collation'))

    Icelandic_CI_AS

    select * from test_data

    Óskar

    Oskar

    Patric

    óskar

    oskar

    Mary

    Ðtest

    Dtest

    ðtest

    Etest

    etest

    Ztest

    Ötest

  • September 8, 2006 at 1:37 pm

    #659447

    First if you want to order something you must use Order by in the select.

     

    Second, check what is the collation of the column in question.. You should see that is may still be the old one.

     

    If it's the case : Select * from dbo.MyTable order by YourCol COLLATE Icelandic_CI_AS

  • September 8, 2006 at 2:58 pm

    #659464

    Thanks a lot. I have the order by clause, of course - just missed it when typed the post - sorry.

    But that still do not help.

    As you stated after changing the collation I still see the old sort order - why? Is there something I could do to refresh it, or it'll be available only for newly inserted data?

    I cannot use "COLLATE Icelandic_CI_AS" as the code is in a 3rd party application and I cannot change it.

    Thanks lot,

    mj

  • September 8, 2006 at 5:59 pm

    #659492

    Where did you change the collation setting?

    If I remember correctly the only place it could have an effect would be at column level and nowhere else.

  • September 9, 2006 at 4:49 pm

    #659510

    I changed the collation with the statement above via alter database. as you could in the example, the setting took effect but the sorting is still the old one - the one of the collation before the change - I was expecting that when I change the collation, the ORDER by clause will bring the values ordered by the rules of the new collation. This is apperantly not true. The values come ordered by the rule of the old collation.

    Thanks a lot, mj

  • September 9, 2006 at 8:22 pm

    #659512

    Please check the collation setting at COLUMN level... this is the one that will count. I suspect it hasn't been affected by the alter database statement.

Viewing 6 posts - 1 through 5 (of 5 total)

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