Sql-server – Installed SQL Server 2000 with wrong collation.. how to correct for entire server instance

collationsql serversql-server-2000

I installed SQL 2000 Developer (yes, old, don't ask) on my laptop … with the wrong collation. Oops!

It's showing Latin1_General_CI_AS, but it turns out I need to use SQL_Latin1_General_CP1_CI_AS to match another instance we're working with.

I found some references with Google on how to change the collation for a specific database, but I'd like to change it for the entire server instance. Is there a way to fix this without having to re-install SQL Server 2000? It's a pain with the service pack, security fixes, etc.

Thanks

Best Answer

Quite honestly, I think your easiest approach will be:

  1. backup your user databases
  2. uninstall SQL Server
  3. reinstall SQL Server with the right collation
  4. restore your databases
  5. fix the collation on the user databases

Also you know that SQL Server 2000 is well out of mainstream maintenance, right? And that quite soon there will have been FOUR major releases since then?

Related Question