Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Changing SQL Server Collation After Installation


By:   |   Read Comments (39)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | More > SQL Server Configurations

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

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?

Solution

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 Update:


signup button

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



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.

Thanks!

 


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.

Thanks.

Sue

 

 

 

 


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?

Thanks


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

 Hi,

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.

Thanks,

Hemanth

 

 


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?

 

ThanksJC


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?

 

Vicky


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?

Adri


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.

Douglas.


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.

Douglas.


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.

Douglas.


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

Thanks,

BOB


 



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?

 

Thanks

 


Learn more about SQL Server tools