Collation problem

  • Hi,

    I have restored database from 2000 to 2005.As that have different collation name from tempdb of 2005.SO it gives problem for query.

    Can we change collation of database??

    As i am going to change it from command,it gives error.

    code is

    ALTER DATABASE database_name

    {

    |

    |

    | MODIFY NAME = new_database_name

    | COLLATE collation_name

    }

    [;]

    Is there any other way to chage collation during restore database?

  • [font="Courier New"]alter database yourdb collate new_collation[/font] will change database collation, which will be used for new objects created in this database. Yet if you want to change collation of existing columns, you have to change their collation for each of them separately. look at my blog post, maybe it will help you.

    ...and your only reply is slàinte mhath

  • Maybe I mis-understand this.

    You can run the alter command to change the collation of existing columns, but any data already in the column will keep the OLD collation.

    This could give some problems if tempdb - for example - uses a different collation.

    My answer would be to dump the data out, alter the collations and then re-load.

    If I get time, I'll try to write a script to prove/disprove ...

  • Thanks for your reply..

    Yes i am going to re-install SQL SERVER 2005,where reinstalling it ask for collation setting,that you can set.

    And i am going to set according to my database.

    Hope this will work

  • avinash repe (2/19/2008)


    Thanks for your reply..

    Yes i am going to re-install SQL SERVER 2005,where reinstalling it ask for collation setting,that you can set.

    And i am going to set according to my database.

    Hope this will work

    Ian is right, if you want to keep the data, the best is to script your database, and create it using the right collations. There are many ways of doing it, you could use DTS/SSIS http://support.microsoft.com/kb/325335 or third party tools.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Maybe it's too much work to change your queries but one option is to create your temp tables first...

    --if you do like this...

    Select name into #tmp from Person

    --you can change to this

    Create table #tmp(

    name nvarchar(25) COLLATE DATABASE_DEFAULT

    )

    insert into #tmp(name)

    Select name from Person

    ...it's more code but you will not have any problem with tempdb collation

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

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