Home / server / Questions / 43477
Accepted
Robin
Asked: 2009-07-22 04:06:34 +0800 CST

How do I change the system wide collation setting in SQL Server 2005

  • 772

I've got a recently upgraded SQL Server which I'm getting ready to put live, we've gone from SQL Server 2000 to SQL Server 2005. The collation setting on the 2005 box is different though (I didn't do the upgrade so not sure if something was done wrong during that process). Is is possible/advisable to change the system wide collation setting in SQL Server 2005, or should I just get the installation done again?

sql-server-2005 sql

3 Answers

  1. Best Answer

    This article (also contains link to MSDN on Microsoft) seems to suggest it can be done and includes instructions, although to be honest it looks like it would be easier just to reinstall!

    • 2

  2. You will have to reinstall SQL as this is the only way to change the system databases to the correct collation.

    The default collation is determined by the Windows Locale and I believe there are some 'tricks' when it comes to upgrading from previous SQL versions. Check out http://msdn.microsoft.com/en-us/library/ms143508(SQL.90).aspx

    • 0

  3. That said you can install another instance on the same server with the collation you want to use and then move the database that needs it over without touching anything else on the old instance.

    This allows you to run the 2 together and not be forced to take down the instance with the incorrect setting.

    Unless its not in production, and it doesn't matter so much.

    • 0