Results 1 to 7 of 7

Thread: Collation

  1. #1
    Join Date
    Jun 2004
    Location
    Treviso (Italy)
    Posts
    17

    Unanswered: Collation

    Hi ,

    I would know what is the simplest (and the more reliable) method to convert an entire db from a collation to another...

    Thanks


  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The simplest way to do it is to script out the schema, edit the script to change the collation, then play the script into an empty database container and reload the data. For small (under 2 Gb) databases, this works fine.

    -PatP

  3. #3
    Join Date
    Jun 2004
    Location
    Treviso (Italy)
    Posts
    17
    Thanks ... But what about changing collation id from script?
    I've already seen the possibility to change collation for columns table ....
    Is there no solution to change also DB setting?


    Bye

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    ALTER DATABASE will allow you to change the collation, but read carefully to be sure that is what you want... I suspect that it won't be much help.

    -PatP

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You can use "Alter database" to change the default collation, but this will only affect tables that are created after the change. You could face a lot of collation conflicts, if you are not careful. As for getting all of the columns in all of the tables in one shot, you would probably have to write a cursor to loop through each table and column with a type of (n)char, (n)varchar, or (n)text, and change them individually. Dose this help?

  6. #6
    Join Date
    Jun 2004
    Location
    Treviso (Italy)
    Posts
    17
    So, When I change collation ID of columns table or db this setting will affect only on new data ....

    If it's right there is something that I don't understand.

    Sometime I've worked with several tables inside at the same db .... these table had different collations and JOIN statement between them caused an collation error, but after executing an UPDATE to collation ID of involded columns the problems was disappeared.....Why?

    Maybe is there some implicit conversion between semi-compatible collations or SQL doesn't raise (after upating ID collation) an error but the result of operation can be corrupted?

    Thanks

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    ALTER DATABASE will change the default collation used for new character columns (if you don't explicitly specify a collation). ALTER TABLE will change the collation of existing columns.

    I don't know of any a "magic wand" that will retroactively change the collation of all your existing columns with a single action.

    -PatP

Posting Permissions

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