Changing server collation

  • Way back, our server collation was set to Latin1_General_Bin to satisfy the requirement of some software running on that server. Since then, we've been using that server for other software that requires SQL_Latin1_GeneralCP1_CI_AS.   Since most applications we now have use SQL_Latin1_GeneralCP1_CI_AS, I would like to change the server default to SQL_Latin1_GeneralCP1_CI_AS, and leave that 1 original DB as Latin1_General_Bin.

    What's the best way to do this ?

  • Assuming you are using SQL Server 2000 (SQL 7.0 doesn't support multiple collations on the same server), use rebuildm to rebuild the master database, specifying the new default collation.  Beware, though - this will change the collation of all system databases and could cause you problems if, for instance, your application creates temporary tables in tempdb and attempts to join them with tables from the original database.  Best to leave things as they are if everything is working at the moment.

  • John:

    I have the same issue with one of my servers.  The original use of the server was to host a datawarehouse generated from our mainframe.

    However, overtime I've been asked to host several vendor applications which do not have the same collation.

    I find it better to simply run an alter after the application insatll has setup the database.  Or if the database is created from scratch, just use the drop down list and pick the desired collation.

    USE master

    ALTER DATABASE database 

    COLLATE < collation_name >

    "If it ain't broke....."

     

    JerseyMo

  • Just an FYI for those attempting to use REBUILDM.

    You need to be logged directly onto the console.  It will not work via an RDP connection.  Although, if you have access through a Lights Out interface, you may have success.

     

  • In SQL Server 2000 you don't need that, because you can add the collation to your create database and Table statement.  Rebuild was a requirement for SQL Server 7.0.  Hope this helps.

    Kind regards,

    Gift Peddie

    Kind regards,
    Gift Peddie

  • You can choose the database collation in 2K according to your requierments. RebuildM.exe will wipe out all your logins, DTS packages etc. TBH you are better off changing the default collation and then converting your DB (painful, but worth it in the end). Having a master, or more of a pain, a tempdb at a different collation to your data database will cause you to jump through more hoops that you really want to (yup, this is the battle scarred voice of experience here). BTW has anyone gotr a full list of collations and what the are (for example, what is the code page of latin1_General_bin and is it unicode compliant?)

    "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein

  • I am not sure if you can use the list with SQL Server 2000 but the links below is the complete code page and collation listing for SQL Server 2005.   Hope this helps.

    http://msdn2.microsoft.com/en-us/library/ms144250(SQL.90).aspx

    http://msdn2.microsoft.com/en-us/library/ms180175(SQL.90).aspx

     

    Kind regards,

    Gift Peddie

    Kind regards,
    Gift Peddie

  • I second (third) the trauma of different collations in tempd and operational databases.  Its a right pain in the jacksy.

    Backup your logins, and rebuild away.

    Just remember there is a good chance that any database not in the server collation will suddenly start throwing errors...!

     

  • You can change collation for a database and a column. But if you would like to set your new collation as default, you have to rebuild master database.

  • i tried rebuildm.

    but the sql_latin1_general_cp1_ci_as collation required couldnot be found.

  • I'm trying to change the default server collation, but I don'k know exactly the way to do it.

    When I execute rebuildm, it throws me an error.

    could anyone help me??

  • Another FYI...

    If you're in a situation where you want to consolidate all your collation settings after rebuilding your master, here's an article I wrote for SQLAuthority, including all the source scripts for automating the collation changes. These script capture everything all the way down to column level.

    The only caveat is that these scripts cannot handle Statistics, because Stats can't be scripted out - or at least I haven't been able to find a way to do so.

    http://blog.sqlauthority.com/2009/10/19/sql-server-change-collation-of-database-column-t-sql-script-consolidating-collations-extention-script/

    Hope this is helpful to some.

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

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