Changing server collation

  • homebrew01

    SSC Guru

    Points: 55137

    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 ?

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    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.

  • Jersey Moe

    SSChasing Mays

    Points: 652

    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

  • Brian Cidern-192506

    SSC Journeyman

    Points: 88

    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.

     

  • Gift Peddie

    SSC Guru

    Points: 73570

    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

  • WiltsDBA

    Say Hey Kid

    Points: 661

    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

  • Gift Peddie

    SSC Guru

    Points: 73570

    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

  • RichB

    SSCrazy Eights

    Points: 9651

    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...!

     

  • SQL ORACLE

    One Orange Chip

    Points: 27807

    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.

  • Rishdin Ameer-354721

    SSC Enthusiast

    Points: 137

    i tried rebuildm.

    but the sql_latin1_general_cp1_ci_as collation required couldnot be found.

  • oswaldomm2

    Grasshopper

    Points: 21

    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??

  • Brian Cidern-245711

    Newbie

    Points: 7

    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 12 (of 12 total)

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