December 5, 2013 at 6:03 am
Hi
How to Change Server Level Collation in SQL Server 2012 Stan Edition after installation?
--
Ragu Thangavel
December 5, 2013 at 6:09 am
Refer to the following link http://technet.microsoft.com/en-us/library/ms179254.aspx
December 5, 2013 at 9:46 am
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. 😎
December 5, 2013 at 9:55 pm
Thanks to all
-- Ragu Thangavel
August 8, 2014 at 7:43 am
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
August 8, 2014 at 5:03 pm
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.
August 8, 2014 at 5:52 pm
Thanks for that clarification 🙂 Appreciate it!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 11, 2014 at 5:01 am
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" 😉
September 22, 2015 at 4:15 am
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\administrator command.
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
September 22, 2015 at 8:33 am
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\administrator
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" 😉
September 24, 2015 at 5:23 am
All working and the collation changed. Thank you for your help.
September 25, 2015 at 2:14 am
Why don't you use COLLATE DATABASE_DEFAULT on fields causing a the problem?
January 11, 2016 at 5:01 pm
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