Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Changing SQL Server Collation After Installation

By:   |   Last Updated: 2015-02-19   |   Comments (54)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | More > SQL Server Configurations


In some scenarios we can find different SQL Server collations between the server instance and its databases.  Sometimes the collation is fixed in some table columns or inside stored procedures to solve some of relationship problems with columns that have different collations. The most common technique to have a consistent collation for the instance and all it databases has a lot of steps and can sometimes be problematic.  Can you provide any suggestions?


A simple way to correct the collations in a few steps is outlined in this tip. It is recommended to create a backup of all databases (including system databases) before take administrative actions on a SQL Server instance. It is important to ensure that there is no fixed collation logic in columns or inside stored procedures, triggers, etc., otherwise the command below may report problems.

Step 1 - Determine the SQL Server Collation

Let's confirm the current SQL Server instance collation and all it databases including system databases collation before taking actions.

SQL Server Instance Collation

SQL Server Instance Collation

SQL Server Master Database Collation

SQL Server Master Database

SQL Server DBTest Database Collation

SQL Server DBTest Database

The server has the "Latin1_General_CI_AS" collation and we'll change it to "SQL_Latin1_General_CP1_CI_AI" for this test.


Step 2 - Stop the SQL Server Services

First of all, we have to stop the SQL Server Services for the changes to be applied.

SQL Server Service List

SQL Server Service Stop

Step 3 - Open a Command Prompt and Navigate to the Binn Directory

Now we have to open a command prompt with administrative privileges and go to the BINN directory of Microsoft SQL Server, following the example below:

SQL Server Binnn Directory

This picture shows the SQL Server Binn directory and "sqlservr.exe" that will be used in this test.


Step 4 - Apply a New SQL Server Collation

Execute the command below. A lot of information will appears and no user action is required, just close the prompt window after the execution ends.  The parameter "-s" is only necessary if more than one SQL Server instance exists on the target machine.

sqlservr -m -T4022 -T3659 -s"SQLEXP2014" -q"SQL_Latin1_General_CP1_CI_AI"

Parameters used:
[-m] single user admin mode
[-T] trace flag turned on at startup
[-s] sql server instance name
[-q] new collation to be applied

sqlservr operation


Step 5 - Start SQL Server and Verify Collation

Start the SQL Server instance and confirm that all changes were applied on the instance and all databases including system databases have the correct collation.

SQL Server Binnn Directory

SQL Server Instance New Collation

SQL Server Instance Collation

SQL Server Master Database New Collation

SQL Server Master Database

SQL Server DBTest Database New Collation

SQL Server DBTest Database
Next Steps

Last Updated: 2015-02-19

get scripts

next tip button

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Friday, June 07, 2019 - 3:23:26 AM - Madhu Back To Top

Thanks a lot....it's working fine.

Sunday, April 28, 2019 - 4:46:05 PM - Ehsan Back To Top

 Hi ,Thanks for your excelet solution with helpfull screen shots. It works perfect for me with SQL Server 2014

Friday, January 11, 2019 - 5:33:51 AM - Bella Vandla Back To Top

Thank you very much this was so helpfull aflter along time hoovaring around

Saturday, November 17, 2018 - 4:41:08 AM - Jon Smith Back To Top

Very helpful. Thanks for taking the time to write this.

Friday, October 12, 2018 - 4:29:26 AM - user Back To Top

 Thank you! It helped. 

Wednesday, September 12, 2018 - 5:24:02 PM - mOISES pUESTO Back To Top

 Thanks a lot!


Tuesday, August 07, 2018 - 1:36:29 PM - Eric Wild Back To Top

This worked. You might want to put a note that if you have a lot of databases with many tables it will take some time. I have about 20 dbs and they have 3 - 4 thousand tables. Each index needed to be rebuilt, so this process took 4 hours. 

Tuesday, June 12, 2018 - 1:29:50 PM - Solomon Rutzky Back To Top

Since this option is not well documented, I did a bunch of testing and posted the details here:




Monday, May 28, 2018 - 12:41:47 PM - frank Back To Top

This completely crashed my SQL server. Unable to start any SQL service after that!

Needed to fully reinstalled and restore DB from backup.

Thursday, May 24, 2018 - 6:17:22 AM - Siva Back To Top


 Hi ,

How to change collation in clustering?


Tuesday, May 08, 2018 - 1:43:50 AM - Sagar Back To Top

what is trace flags 4022 and 3659.

Wednesday, April 04, 2018 - 10:55:28 AM - Andy Hilton Back To Top

For SQL Server 2016 the "-q" no longer exists.  Use the following for 2016:

sqlservr -T4022 -T3659 -s"SQLEXP2014" "SQL_Latin1_General_CP1_CI_AI"



Monday, February 26, 2018 - 6:35:03 AM - eqbal Back To Top


it helps me realllyyyyy

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr -m -T4022 -T3659  -q"Persian_100_CI_AI"

Monday, January 08, 2018 - 4:31:41 AM - Franco Landman Back To Top

When executing: sqlservr -m -T4022 -T3659 -s"SQLEXPRESS2014" -q"Latin1_General_CI_AS"

I get a messagebox titled: SQL Server and a red cross but no error message... Please help

Thursday, December 07, 2017 - 3:09:06 PM - Moacir de Oliveira Back To Top



Obrigado. me ajudou muito.


Thursday, October 12, 2017 - 1:35:40 PM - Bill Back To Top

Great article.  Do you have any clue why this error was rasied?  I got it on a few procs but I dug through the dependencies and it appears the collation was changed.


"Parsing errors were encountered in the procedure 'ProcName'. The dependencies referenced from this object could not be determined"



Friday, August 18, 2017 - 3:13:26 PM - ondo Back To Top

I would mention here , that every DB that is on instance on which you are running this command to change collation, needs to be readable, or even read/write. I have tried this on AlwaysOn AG, where secondary was just standby, not readable, and when I stopped SQL service and started it via cmdline, the change process failed because user DB was not readable.

However, very usable option how to change the collation.



Tuesday, August 01, 2017 - 12:32:02 PM - Sue Back To Top

 Hi, thanks for your post.

I need a help.

I did everything you said in this article, but didn´t work.

This the message the appears in the end:

"error: 3434, severity: 20, states:1

Cannot change sort order or locale. An unexpected failure ocurred while trying to reindex the server to a new collation. SQL Server is shutting

down. Restart sql server to continue with the sort order unchanged. Diagnose and correct previous erros and then retry the operacion"

Can you help me to resolve this? I google it and i didn´t find the solution.







Wednesday, July 12, 2017 - 11:24:02 PM - julian Back To Top

 Hi , this post is great,  tenk soo much . 




Thursday, April 20, 2017 - 7:10:12 AM - Gbemi Back To Top

Thanks for this, Doug.  Works a treat.  Saved me a reinstall.  Luckily the instance was not commissioned yet.Just a note to say CMD needs to be launched as an Administrator.

Great Post!

Tuesday, April 11, 2017 - 8:00:24 AM - Roger Baten Back To Top

I have a tip regarding this solution whenever your instance hosts SSIS catalog database (SSISDB):

You have to detach database SSISDB _before_ performing the procedure, or it will break (you'll get a nice collation error when opening the catalog in SSMS :-)) and you will have to restore it. After the collation is updated and sql is running fine again, re-attach the SSISDB database.

Friday, March 24, 2017 - 3:18:59 AM - Reza Back To Top

I did apply above thread in SQL Server 2016 (SP1). result was successful against System DBs (Collation was updated). However, Still SQL Server Collation shows the previous collation.  any reason Server Server Collation did not update. thanks for the sharing

Friday, December 30, 2016 - 1:51:08 AM - Najeem Back To Top


 Thaks, it is very knowledgeable...

Monday, November 28, 2016 - 5:00:32 PM - Rajiv Singh Back To Top

 Hello There, to start with- I must thank you for helping me fix the issue. I looked on million website and non helped excluding you. I cannot tell you how happy I am after getting this fixed. Thanks a million. You are the real SQL boss.


Thursday, November 24, 2016 - 11:17:35 PM - julian castiblanco Back To Top


Thank you. It works perfect for me with SQL Server 2012

Thursday, November 03, 2016 - 6:55:00 AM - N03L Back To Top

Thanks for the info.
It worked in the most wonderful way :-)

Thursday, October 27, 2016 - 5:33:39 AM - Steven Lowenstein Back To Top

 Very helpful.   Worked first time.  Thank you.

Tuesday, October 25, 2016 - 5:48:36 AM - Shalalal Back To Top

Thank you for this solution. Does this procedure work on a sql server 2008r2 clustered instance?

Tuesday, October 11, 2016 - 11:53:51 PM - ratthapong Back To Top

 Thank you So Much


Thursday, September 29, 2016 - 7:07:51 PM - Chakib IBS Back To Top

Thanksssss youuu , it solve my probleme :)  


Saturday, September 10, 2016 - 1:07:33 AM - Sarika Arora Back To Top

This is the best solution I found online to change the SQL Server 2008 R2 Collation on server level. Thank you so much dude ! It really helped me to solve my problem by setting the right collation for sql server. 


Monday, August 22, 2016 - 8:10:35 AM - Alex Athanasopoulos Back To Top

Thanks was the best i could find online helped and solved the problem.

Keep up the good Job mate!

Sunday, May 15, 2016 - 10:43:42 PM - Zinx Back To Top

Thanks for sharing your knowledge. 


I followed the steps, it has changed collation of all the sys DBs and other DBs but not Server's collation. 

If I check properties of the instance in SSMS its still showing old collation. Any suggestion?


Friday, May 13, 2016 - 2:50:19 AM - max Back To Top

Thank you, its worked for SQL Server 2008 R2

Thursday, May 12, 2016 - 5:39:52 AM - Thanapat Supphathanthada Back To Top

Big thanks. That's very helpful :D

Wednesday, April 20, 2016 - 2:30:43 PM - Pierre Back To Top

Thanks so much !!!

SQL Server was pre-installed on DELL server with a collation that conflicted with my existing database.

Attached the database and ran into problems when temp tables were created. This simple and clear procedure did the job.

The only thing to add to this documentation is a 'CTRL C' keypress once the sqlserver command has completed. This will release and allow a shutdown.

Saved the day :)

Wednesday, March 23, 2016 - 2:57:51 AM - Yogesh Back To Top


 Nice article. it should help to solve collation proble.

Friday, March 18, 2016 - 1:21:56 PM - Hemanth kumar Back To Top


I have upgraded to SQL 2012 from SQL 2008 R2. I need to Change the collation for Upgraded version(SQL 2012). i.e SQL_Latin1_

General_CP1_CS_AS to Latin1_General_100_CS_AS_SC. Can you please suggest the process.





Sunday, March 13, 2016 - 12:46:56 PM - John Back To Top


Hi Experts,


Does this steps above will work on SQL Server 2008 R2 SP1?



Tuesday, March 08, 2016 - 12:09:25 PM - William Brasil Back To Top

 Very useful, after read a lot of other "how to", this is the only one that worked. Other sites doesn't show how to stop the SQL SERVER by command, then the sqlserv command will not work.
Just need to perform Control C after sun the sqlserv command, finally "net start..." .



Tuesday, February 02, 2016 - 6:25:42 AM - manu Back To Top

Thaks Jason for the useful tip. 


Wednesday, December 16, 2015 - 4:04:19 PM - Jason M. Back To Top

Didn't see anyone else mention this so not sure if new issue related to SQL2014  ( FYI this worked like a charm on SQL2014) but between steps 4 and steps 5 the user needs to hit CTRL-C to indicate to running process to shut down.  If the user opens SERVICES to restart SQL and SQL Agent the process bombs because error logs are in use from single user mode and the running instances does not appear as running when in single user mode so the user tries to start the SQL instance , bombs out after running the collation update and if you are like me your mind floods with all the worse case scenarios.   A little debugging and then saw the SQLSERVR was actually still running and tried CTRL-C and then it closed down and could then start normally using SERVICES to start SQL and SQLAgent.   Cost me a couple gray hairs so could not hurt to add mention of using CTRL-C when the windows comes up and shows the tail end of the process being completed so the user knows to end the running instance of SQL before trying to restart the same instance again.     Not seeing anyone else mention this below I guess everyone else regularly starts / stops services from command line so the addition of the mention above in the process would be for those of us who don't.


Monday, October 12, 2015 - 8:14:38 PM - Arjun Sivadsan Back To Top

Thanks mate. Your post helped me :)

Saturday, August 01, 2015 - 11:06:01 AM - Anil Kumar Back To Top

How can I use MSSql as a C# .NET, Windows 10 developer?

Saturday, August 01, 2015 - 11:01:12 AM - Anil Kumar Back To Top

Very-very useful.


I'm using MSSql as server for Team Foundation Server. For that I needed to change collation to Accent Sensitive from Insensitive.

Friday, February 27, 2015 - 12:48:32 PM - Gianluca Sartori Back To Top

@Vicky I'm completely sure it has been working since SQL Server 2005. I have no idea whether prior versions supported this startup parameter, but I suppose they didn't.

Friday, February 27, 2015 - 11:34:27 AM - Vicky Simpson Back To Top

Hi Douglas,

Thanks for the post. This is a long time waiting for. Is this available in SQL 2012 or only SQL 2014?



Monday, February 23, 2015 - 2:56:22 AM - Adri Koopman Back To Top

Hi Douglas,

Does this also change the collating sequence on the individual table columns with a (n)(var)char data type?


Thursday, February 19, 2015 - 11:22:05 AM - Douglas P. Castilho Back To Top

Hello Gianluca,

You are alright! Sorry about that!

This tip helps to change collation with more security after sql server installation, when you are ready to prepare the new environment, that it´s more difficult to be reported with any issues or have something broken. In a production environment, with all the things running perfectly, follow what I said inline to ramesh bob.

Thank you for your important feedback.


Thursday, February 19, 2015 - 11:06:42 AM - Douglas P. Castilho Back To Top

Hi ramesh,

My recommendation for you is to replicate your production environment in another instance and change the collation and see if everything is ok. As I said in this tip, please create a backup of all databases before change the production sql server collation.

Remember, this is not a simple change in a production environment!

We could be reported with any issues when different collations already exists inside code, and we have to analyze the report and fix everything before new try.

In an auxiliar instance we could get all fix needs to apply them in the production instance.

Thank you.


Thursday, February 19, 2015 - 9:45:11 AM - Gianluca Sartori Back To Top

You didn't mention that this method is undocumented and unsupported. If you break something, MS won't help you.

Thursday, February 19, 2015 - 9:11:04 AM - Douglas P. Castilho Back To Top

Hello Galina,

Everything is kept after change, don´t worry about that.

Thank you.


Thursday, February 19, 2015 - 8:54:59 AM - ramesh bob Back To Top

Hi Douglas,

Thanks for posting this,

May I know would there be any issues if the tsql code already uses SQL_Latin1_General_CP1_CI_AI.

For instance there are some sps whose collation changed explicitly in tsql code with SQL_Latin1_General_CP1_CI_AI as we can not go back and re install the instance and change the instance

However, we are going to change the collation as desired if we migrate 

The question here is were there any issues if we change the collation in existing perfectly running production environemt




Thursday, February 19, 2015 - 7:56:19 AM - Galina Back To Top

Hey  Douglas!


Thank you very much for your post.

Can database master keep objects (e.g. logins)  which had been  created before the collation was changed?




Learn more about SQL Server tools