SQL Server Performance Forum – Threads Archive

collation problem

How 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. _________
Satya SKJ

---

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
Webmaster
SQL-Server-Performance.Com

---

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. _________
Satya SKJ

---

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. _________
Satya SKJ

---

]]>