SQL Server Performance Forum – Threads Archive
collation problemHow can i solve the problem of having different collation types when migrating sql 7 databases to a new sql 2000 server. the collations seems to always fall out.
In SQL 2000 you can set COLLATION setting on database/table/column levels. Whereas in 7 you need to rebuild the database using REBUILDM utility. Refer to books online for ALTER DATABASE statement to change the collation settings for database. _________
doing that is just for the session and once i close the session it falls over again.
Can you explain a little more about what you mean by "falling out"? —————————–
Brad M. McGehee, MVP
well what happens is if i restore a database from a sql 7 server to a sql 2000 server i get a different collation between the database from the sql 7 and the sql 2000 servers. so what i’ve done is to install the sql 2000 server with the same sort code id as the sql 7 server before restoring the database so that the sql 2000 server would have the same collation settings as the sql 7 server.
Now that u have migrated the databases to same collation, as Brad said, use ALTER DATABASE to change the collation for the database. Gaurav
i don’t think i need to use alter database anymore because the collation of the database i restored is the same as the collation of the server i restored unto, due to picking the right sort code id during installation of the sql2000 server.
Yes as of now, but you can avoid reinstalling SQL 2K using ALTER DATABASE itself. _________
Note that changing collation on a database with alter database will only affect new columns you create. Existing columns will keep their old collation. /Argyle
True, for instance on the empty database if you affect the collation changes, after restore it will have collation with new changes. _________