How to change SQL Server Collation

  • chmod260

    SSC Veteran

    Points: 257

    Hello, this message was previously posted in the General Discussion and got no answer. Perhaps this is the correct place…

    I was trying to install Visual Studio Team Foundation Server but the final check failed because my SQL Server 2005 installation had the wrong Collation setting:

    ————————————-

    The System Health Check has detected a problem that will cause Setup to fail.

    Description

    SQL Server collation is not set to one supported by Team Foundation Server.

    Workaround / Remedy

    The SQL Server collation is not set to one supported by Team Foundation Server. Change your collation to one that is supported and run setup again.

    ————————————-

    I googled around for a while and found that TFS requires a collation that is case insensitive and accent sensitive. My collation was accent INsensitive.(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=275420&SiteID=1)

    To change Server Collation in SQL Server 2000 I usually use the rebuildm file but this is not supported anymore in SQL 2005.

    Microsoft recomends setup.exe to rebuild the master database (http://msdn2.microsoft.com/en-us/library/ms143269(SQL.90).aspx)

    and Tom Wisnowski details the operation (http://geekswithblogs.net/mskoolaid/archive/2005/12/17/63413.aspx)

    I tried this solution but… the master database was rebuilt but no change to change the collation (at least within a visible GUI).

    Question: How do you change the SQL Server 2005 Collation? Do you MUST rebuild the master database?

    —————————————————————–


    Regards,
    chmod

  • dharper3

    SSCrazy

    Points: 2341

    Hmm can’t say I’ve tried on 2005 either.  One way would be via add / remove programs uninstall sql server db engine and re-install.  During install you need to ensure you choose a windows collation thats suitable for TFS, not sure of your region settings on the server but a suitable one would be Latin1_CI_AS. 

    If I have time I might try to mod one here and advise if the add/remove allows a modification / upgrade to the colation or not.

    Derek

  • dharper3

    SSCrazy

    Points: 2341

    Straight from BOL,  the source of all knowledge:

    To rebuild system databases and specify a new system collation

    Insert the SQL Server 2005 installation media into the disk drive.

    Run the following command from the command prompt:

    start /wait <DVD Drive>\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation>

     

    For INSTANCENAME, use MSSQLSERVER for a default instance. For a named instance, specify the instance name.

    Important   The resource database (mssqlsystemresource) is restored from SQL Server 2005 distribution media. When you rebuild the resource database from distribution media, all Service Packs and QFE updates are lost, and therefore must be reapplied. Before you proceed, see the section below on REBUILDDATABASE for more information.

    The /qn switch suppresses all Setup dialog boxes and error messages. If the /qn switch is specified, all Setup messages, including error messages, are written to Setup log files.

    The /qb switch allows display of basic Setup dialog boxes. Error messages are also displayed.

    Hope this helps.

    Derek

  • bubu Shtef

    SSC Enthusiast

    Points: 148

    Try:

    ALTER DATABASE MyDB COLLATE SQL_Latin1_General_Cp1250_CS_AS

     

     

  • dharper3

    SSCrazy

    Points: 2341

    That allows change of a databases collation not the sql server instances collation.

  • raj-247939

    SSCertifiable

    Points: 5939

    The server collation acts as the default collation for all system databases that are installed with the server, and also any newly created user databases. The server collation is specified during setup

    If u want to change the collation u have to rebuilt the master database.

    If u dont want to do that then u can do it like this (reinstall sqlserver)

    start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

     

    from

    Killer

  • chmod260

    SSC Veteran

    Points: 257

    Thank you to all!

    SQLCOLLATION= was de missing part of the equation…

    Also the ALTER DATABASE MyDB COLLATE NewSystemCollation> tip is very interessting as a per database change… (does it work win sql 2000?)

    Thanks


    Regards,
    chmod

  • dharper3

    SSCrazy

    Points: 2341

    Yes sql2000 supports multiple collations ‘each of which can be set to a different collation’ at the sql instance level, the database level, the table level and columns within a table.

    By default if you specify no collation on a create db, the database takes on the instances default collation (its actualy based on whatever the model db’ collation is), objects within a db (again if you do not specify) take on the databases default collation. You can specify a different collation for any of these objects.

    Note if you perform a db restore the collation of the database will be retained. i.e backup a db and its collation was ‘latin1_general_CI_AS’, then restore to sql instance which has a default of ‘sql_LATIN1_CP1_CI_AS’ and the database retains its collation of ‘latin1_general_CI_AS’.  Something to be aware of when restoring vendor supplied databses etc onto your systems.

  • BPv20

    SSC Journeyman

    Points: 88

    Hello All –

    I am having a problem rebuilding the master db (for a collation change) on an installed SQL 2005 instance. I am recieving the error: “The installer has encountered an unexpected error. The error code is 2711. The specified feature name (“SQL_ENGINE”) not found in feature table.

    I have put the current instance in single user mode via the sqlservr -m command and executed the command:

    start /wait E:\media\SQL2k5\SQLENTSEL_2K5\Servers\setup.exe INSTANCENAME=MSSQLSERVER REINSTALL=SQL_ENGINE REBUILDDATABASE=1 SAPWD=XXXXXXXX SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

    Unfortunately, I can’t find much info on this error. My last resort is going to be to add/remove via the control panel, but haven’t gotten there yet. any suggestions?

    B

  • Roger Williams-357196

    SSC Enthusiast

    Points: 156

    Have you found the solution yet? I’m still having problems

  • Roger Williams-357196

    SSC Enthusiast

    Points: 156

    Found the problem. You are using the wrong version of setup.exe

  • MikeTomkies

    Hall of Fame

    Points: 3045

    Hi i am using the above stated method to try and reinstall to correct server collation. the setup runs Ok up to chosen to upgrade and i continue. the problem is i then get a message that “an installation package for microsoft sql server2005 can not be found.try the installation again using a valid copy of the installation package ‘sqlrun_sql.msi'”

     

    I am use sql 2005 developer edition with service pack 1. is pack causing this file to be invalid. i am using the developer edditio install setup.

    any help appreciated.

  • MikeTomkies

    Hall of Fame

    Points: 3045

    I have foun the solution. msi location is cached in the registry. Explained below.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=949298&SiteID=1

  • bp01

    Valued Member

    Points: 56

    Nope, no solution, but I think the problem was a bad install package.

  • Sugesh Kumar

    One Orange Chip

    Points: 27311

    You cant change the collation of the server once installed. you need to reinstall with new collation.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

Viewing 15 posts - 1 through 15 (of 77 total)

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