Change SQL Server 2012 Collation

  • Hi

    How to Change Server Level Collation in SQL Server 2012 Stan Edition after installation?

    --

    Ragu Thangavel

  • Refer to the following link http://technet.microsoft.com/en-us/library/ms179254.aspx

  • There's also a workaround that I saw once here:

    1.-Stop SQL Server service from Configuration Manager

    2.-Open CMD console as administrator and go to the following path: C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012(Here depends on the name of the instance)\MSSQL\Binn (or the path where your binary files are)

    3.- Execute the following command: sqlservr.exe -sYOURSERVERNAME -m -T4022 -T3659 -q"DESIREDCOLLATIONNAME"

    4.-Wait until it finish, start the SQL Server service again and enjoy. 😎

  • Thanks to all

    -- Ragu Thangavel

  • hiram.osiris (12/5/2013)


    There's also a workaround that I saw once here:

    1.-Stop SQL Server service from Configuration Manager

    2.-Open CMD console as administrator and go to the following path: C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012(Here depends on the name of the instance)\MSSQL\Binn (or the path where your binary files are)

    3.- Execute the following command: sqlservr.exe -sYOURSERVERNAME -m -T4022 -T3659 -q"DESIREDCOLLATIONNAME"

    4.-Wait until it finish, start the SQL Server service again and enjoy. 😎

    Does this "really" work? I mean, what about needing to rebuild the system/user databases?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • MyDoggieJessie (8/8/2014)


    hiram.osiris (12/5/2013)


    There's also a workaround that I saw once here:

    1.-Stop SQL Server service from Configuration Manager

    2.-Open CMD console as administrator and go to the following path: C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012(Here depends on the name of the instance)\MSSQL\Binn (or the path where your binary files are)

    3.- Execute the following command: sqlservr.exe -sYOURSERVERNAME -m -T4022 -T3659 -q"DESIREDCOLLATIONNAME"

    4.-Wait until it finish, start the SQL Server service again and enjoy. 😎

    Does this "really" work? I mean, what about needing to rebuild the system/user databases?

    Well it works most of the times, but you should have precaution, it actually rebuilds all user/system database objects, but if you have schema bound objects (i.e. Constraints), the whole operation will fail.

  • Thanks for that clarification 🙂 Appreciate it!

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • hiram.osiris (8/8/2014)


    There's also a workaround that I saw once here:

    1.-Stop SQL Server service from Configuration Manager

    2.-Open CMD console as administrator and go to the following path: C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012(Here depends on the name of the instance)\MSSQL\Binn (or the path where your binary files are)

    3.- Execute the following command: sqlservr.exe -sYOURSERVERNAME -m -T4022 -T3659 -q"DESIREDCOLLATIONNAME"

    4.-Wait until it finish, start the SQL Server service again and enjoy. 😎

    Note: The -s parameter expects the instance name and not the servername. For a default instance you can omit this parameter totally.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hello,

    I also need to change the server collation. I have a 2 node, 2 instance SQL 2012 SP1 cluster. I tried to run the sqlservr -m -T4022 -T3659 -s"SQLEXP2014" -q"SQL_Latin1_General_CP1_CI_AI" command. It runs for a while and then fails.

    As per previous post, I thought about rebuild the master DB using the setup /q /action=rebuilddatabase /instancename=sccm /sapwd=Pa$$w0rd /SQLCollation=SQL_Latin1_General_CP1_CI_­AS /SQLSYSADMINACCOUNTS=domain\administrato­r command.

      Is there anything else apart from dropping the users DB i need to be aware of?
      Do I need to run this command on both nodes?

    Thank you in advance

    Peter

    PS: Just found this article, will it still apply to SQL 2012? http://www.sqlservercentral.com/articles/Collation/70685/ Thanks

  • Wooju (9/22/2015)


    Hello,

    I also need to change the server collation. I have a 2 node, 2 instance SQL 2012 SP1 cluster. I tried to run the sqlservr -m -T4022 -T3659 -s"SQLEXP2014" -q"SQL_Latin1_General_CP1_CI_AI" command. It runs for a while and then fails.

    Use setup for reliability, as you have below

    Wooju (9/22/2015)


    setup /q /action=rebuilddatabase /instancename=sccm /sapwd=Pa$$w0rd /SQLCollation=SQL_Latin1_General_CP1_CI_­AS /SQLSYSADMINACCOUNTS=domain\administrato­r

    Wooju (9/22/2015)


      Is there anything else apart from dropping the users DB i need to be aware of?
      Do I need to run this command on both nodes?

    Thank you in advance

    Peter

    PS: Just found this article, will it still apply to SQL 2012? http://www.sqlservercentral.com/articles/Collation/70685/ Thanks

    why drop the user database?

    you only run the setup command once from the active node

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • All working and the collation changed. Thank you for your help.

  • Why don't you use COLLATE DATABASE_DEFAULT on fields causing a the problem?

  • Hey Hiram

    You are a legend.

    I had this problem and searched the Internet high and low to find a relatively simple solution.

    I found many complex difficult solutions that I was not prepared to follow then I found yours.

    It worked like a treat and it was simple.

    Thanks very much

    kind regards

    Steve 🙂

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

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