3

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

Improve this question

2 Answers 2

Reset to default
7

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?

Improve this answer
3
  • 3
    Aware, yes, thanks. It's for some maintenance work on a legacy project. Management have been yelled at for refusing any upgrade. I'll hold out hope for another easier answer before accepting yours .. but I suspect you might be right.
    – my_mistake
    Commented Feb 14, 2012 at 20:25
  • I am sure there are other ways to do it, but I can't imagine changing the collation for the system databases will be fun at all, and I think figuring out how to do it will take longer than just doing it the well-defined way.
    – Aaron Bertrand
    Commented Feb 14, 2012 at 20:32
  • 1
    There is no need to hold off :) reinstall is the only supported way of changing the instance collation.
    – Remus Rusanu
    Commented Feb 14, 2012 at 21:53
2

I found a reference about an undocumented method about changing a full instance collation coming from Paul Randal.

Info quoted from the blog of SpaghettiDBA:

"The method relies on a startup parameter that allows changing the server collation when combined with a couple of trace flags:

sqlservr -m -T4022 -T3659 -q"new collation"

Trace flag 3659 allows logging all errors to sql server logs

Trace flag 4022 forces SQL Server to skip startup stored procedures (if you have any).

Startup option “-m” forces single user mode.

Startup option “-q” rebuilds all databases and contained objects into the specified collation, without reinstalling the instance."

Paul's document with undocumented params is: Undocumented tools and trace flags.

Not very sure it's working on SQL 2000, because I didn't see a clear reference about the server version, but it's worth a try.

I'm also not very sure that it's so hard to rebuild the master db, as in SQL 2000 there was a nice little tool that did that without any user intervention (Rebuildm.exe - found in ..\Program Files\Microsoft SQL Server\80\Tools\Binn directory.).

If you have already some databases to convert you might try the project SQL Server 2000 Collation Changer - it's useful for changing the collation for a database and all underlying objects.

Improve this answer
2
  • Any of this sound easier than paving and reinstalling SQL Server? Really? Not to me. @Remus' point about it not being supported is pretty valid too, though for SQL Server 2000 I guess the point is pretty moot.
    – Aaron Bertrand
    Commented Feb 15, 2012 at 1:34
  • I would try it just for the fun of it, not for the ease, but you're right, it's not very friendly.
    – Marian
    Commented Feb 15, 2012 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.