Post reply

How to change Server collation for SQL2000?

  • January 22, 2004 at 9:27 am

    #65780

    Unfortunately, the SQL2000 server was not installed as a "custom", and the collation defaulted for some reason to "Latin1_General_BIN".  I now have a vendor saying that even though the collation of their database is "SQL_Latin1_General_CP1_CI_AS", the server collation is causing issues with their product.  The SQL2000 server is running other 3rd party db applications in addition to the one, so minimal downtime is key.  Please help!

    Regards, Melissa

  • January 22, 2004 at 10:14 am

    #491151

    Looks like you need second instance.

  • January 22, 2004 at 11:07 am

    #491174

    Yes, another instance with matching collation (server and database) is available so I can move existing applications.  However, the question remains "How to change Server collation for SQL2000".  If the answer is:  Rebuild, then fine.

    Regards, Melissa

  • January 22, 2004 at 11:10 am

    #491176

    Either reinstall or rebuild master database.

  • January 22, 2004 at 11:16 am

    #491181

    To piggy-back on Allen, rebuildm.exe is how you rebuild the master database.

    K. Brian Kelley
    @kbriankelley

  • January 23, 2004 at 2:34 am

    #491293

    Been there before. There are times when reinstalling SQL server is possibly the best option if there are not too many database already attached to it. Fiddling around with rebuidling the Master db could take as much time and possibly lead to other issues and downtime too. Do weigh the pros and cons. I am afraid I have not personally seen or read an article with regards to "changing the collation" after the installation. Thought Microsoft should do more to explain this grey area. Anyway, I am interested if anyone out there has the perfect solution

  • January 23, 2004 at 6:42 am

    #491326

    Having not used SQL2K (but will be soon), collation is more of a black art than a grey area for me. I thought that SQL2K allowed different collations for different databases on the same server (and even by column?). Are there problems (3rd party systems aside) with sql where a database collation is different to the default? How do you decide what the default should be?

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 23, 2004 at 7:12 am

    #491344

    Here where I work we tend to use the default collation for the server and specify the collation on the database if that's required unless we know the SQL Server is going to be wholly dedicated to a particular application with a specific collation. We've not run into any issues with 3rd party products so far as I am aware.

     

    K. Brian Kelley
    @kbriankelley

  • January 23, 2004 at 7:12 am

    #491342

    Yes, you can have different collations in SQL 2000. I think a way to change the collation would be backup restore you db as another name, drop your live db, recreate it and then use DTS to create objects and transfer data into db. The collation is how the data is stored in the db and this needs to be corrected in the db so the only way is to reload the data.

  • January 23, 2004 at 3:58 pm

    #491483

    I have just resolved a similar situation because ofa third party software (CRM) that requires a specific collation.

    I used rebuildm and is quiet easy if you know what to do and faster than re-installing the server. Followin the following steps

    1. Back up your user databases as well as master, model and msdb.

    2. Copy the directory x86 from the installation disk to your hard drive and remove read only on all files in the directory/subdirectory.

    3. run rebuildm from the x86/binn which is already copied to your hard drive.

    4. in the GUI, select source directory as x86\Data

    5. Use the collation designator to select a new collation setting.

    6. Click on rebuild.

    7. When finished, restore the user databases, msdb and model. Do not restore master as this will return you back to the old collation.

    8. The sa password is now blank and you will need to change it.

    That is all you need to do.

    A faster way is to detach and re-attach the user dbs instead of backing them up.

    I have used this method for 3 different production servers without any problem.

  • January 23, 2004 at 4:55 pm

    #491488

    Thank-you!!! That is exactly what I did -- with an additional first step:  Offline the SQL Server in the Cluster Server Administrator.  It really is a 10-minute job (plus restores, of course).  I should have followed your very detailed instructions instead of those in the "How to rebuild the master database" in the MSSQL online books (they neglected Step 2, and the rebuildm failed).  Again, thanks.

    Regards, Melissa

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply