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
:confused: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|||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 :cool:|||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|||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?|||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? :confused: :confused:

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|||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.

-PatPsqlsql