none
Collation of database objects RRS feed

  • Question

  • Hi, we have changed the collation of our server (2005). Now we have database objects created with the original collation that are failing due to collation conflicts. How can I change the collation of these objects to the new collation?
    Jonathan
    Tuesday, November 16, 2010 9:21 PM

Answers

All replies

  • Kiwi,

    What objects are you talking about? I know that when you create a table, you need to specify COLLATION DATABASE_DEFAULT. I'm guessing you can also change collation with the ALTER TABLE statements if you're talking about column's collation, although it may lead to problems.

    One of the other possibilities is to create table with the right structure and import data from table with 'bad collation'.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, November 16, 2010 9:31 PM
    Moderator
  • Hi Jonathan,

    When you change the collation, the new collation will be used only for the new objects. For the existing objects you cannot change. Only solution would be to recreate the tables and import the data as suggested by Naom.

    Regards,

    Ramesh

    Tuesday, November 16, 2010 10:17 PM
  • Yes, you can change the collation of DB objects even after changing the Database default collation.

    I've done this in SQL Server 2005 without any glitch, here is the link: http://sqlwithmanoj.wordpress.com/category/collate/

    You can try this, if there comes any problem then try the solution Naomi suggested to recreate tables & import data.


    ~Manu
    http://sqlwithmanoj.wordpress.com
    • Marked as answer by Kiwi Jonathan Sunday, November 21, 2010 9:33 PM
    Wednesday, November 17, 2010 12:33 AM
  • You can change the collation of the server, a database (ALTER DATABASE), a column (ALTER TABLE), but that will not affect existing data. You will need to script your objects (careful to script the collation), export you data using for example BCP, change the collation of the server, recreate your databases / objects and import the data.

    Setting and Changing the Server Collation


     


    AMB

    Some guidelines for posting questions...

    Wednesday, November 17, 2010 1:08 AM
    Moderator
  • Hi, we have changed the collation of our server (2005). Now we have database objects created with the original collation that are failing due to collation conflicts. How can I change the collation of these objects to the new collation?


    Welcome to the nightmare of collation conflicts!

    Your best friend until the final solution: COLLATE DATABASE_DEFAULT. Related link:

    http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/

    The real solution is:

     o Rebuild tables with matching collations.

     o Change column collation one by one. Related link: http://www.sqlusa.com/bestpractices/changecollation/ 

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, November 17, 2010 1:38 AM
    Moderator