Changing SQL Server Collation After Installation

By:   |   Updated: 2019-08-30   |   Comments (70)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | > SQL Server Configurations


Problem

In some scenarios we might 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 relationship problems with columns that have different collations. The collation differences may have been setup purposely, but in other cases they may have been a mistake. In this tip we will look at different ways to change the SQL Server collation for the instance and databases and things to be aware of when making these changes.

Solution

Before proceeding with making changes following the steps below, you should consider any issues that might occur by changing the collation either at the system level, database level or column level.  In some cases things may have been setup purposely to handle specific needs, but this also may have been a mistake that was made during installation and you want to make sure all databases conform to your specifications.  You should test these processes in your development environments before implementing in production.

Techniques that will be covered to change SQL Server collation

In this tip we will look at three different approaches and the outcome of each approach testing with SQL Server 2005 through 2019.

  • Transact-SQL script
  • Undocumented script
  • Setup with SQL Server parameters

The purpose of this tip is to change all system databases and user databases to use the new collation setting.  The different scenarios above will show what actually gets changed so you can determine which approach makes sense.

The techniques below that will be covered make the DBA's daily work much easier, as well as provide a means to revert a misapplied configuration after the environment has been completely configured, but we always have some caveats to consider.

NOTE: We will make use of some of the available solutions, but problems may occur during their execution and may even cause some issues, so it is very important to note that these features should be tested prior to running in a production environment.

Environment Preparation for SQL Server Collation Change

In this first step we will simulate the situation with a new environment, we have the system databases and one user database created for testing purposes.

Once the environment is created and configured, as shown in the images below, we can view the Collation configured for both the instance, the system databases and user database.

SQL Server Service List

Available Techniques for Collation Switching in SQL Server

At this point we will get to know the available techniques, understand them, and know the pros and cons of use, know which versions of SQL Server are compatible with each one and then apply them in our isolated environment for testing, but before that let's get to know a bit more about Collation.

According to Microsoft documentation, we can get a glimpse through the quotes below from the following links:

Collation:

A collation specifies the bit patterns that represent each character in a data set. Collations also determine the rules that sort and compare data. SQL Server supports storing objects that have different collations in a single database. For non-Unicode columns, the collation setting specifies the code page for the data and which characters can be represented. Data that is moved between non-Unicode columns must be converted from the source code page to the destination code page.

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and varchar dictate the code page and corresponding characters that can be represented for that data type. Whether you are installing a new instance of SQL Server, restoring a database backup, or connecting server to client databases, it is important that you understand the locale requirements, sorting order, and case and accent sensitivity of the data that you are working with

When you select a collation for your server, database, column, or expression, you are assigning certain characteristics to your data that affects the results of many operations in the database. For example, when you construct a query by using ORDER BY, the sort order of your result set might depend on the collation that is applied to the database or dictated in a COLLATE clause at the expression level of the query.

Locale:

A locale is a set of information that is associated with a location or a culture. This can include the name and identifier of the spoken language, the script that is used to write the language, and cultural conventions. Collations can be associated with one or more locales.

Code Page:

A code page is an ordered set of characters of a given script in which a numeric index, or code point value, is associated with each character. A Windows code page is typically referred to as a character set or charset. Code pages are used to provide support for the character sets and keyboard layouts that are used by different Windows system locales.

Sort Order:

Sort order specifies how data values are sorted. This affects the results of data comparison. Data is sorted by using collations, and it can be optimized by using indexes.

Option # 1: Using Transact-SQL to Change SQL Server Collation

In this option we can use this approach to change collation for User Database and Columns.

Pros

  • Easy collation change of user databases and their objects

Cons

  • It is not possible to change the master database collation via T-SQL, for this we will see how to do that in scenarios 2 and 3.
  • Changing the collation of the user database will not change the collation of the table columns belonging to the database.  This has to be done column by column.

Step 1

Let's confirm the current collation assigned to database Products:

SELECT name, collation_name FROM sys.databases WHERE name = 'Products';

Below we can see it is set to Latin1_General_CI_AS.

SQL Server Service List

Step 2

Now let's confirm the collation of the ProductGuid table columns:

SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductGuid'

We can see that the guidd column is also using the same collation Latin1_General_CI_AS.

SQL Server Service List

Step 3

We can now redefine the desired collation for database Products with the following code:

-- make sure no one else is using database
ALTER DATABASE Products SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- change collation to Modern_Spanish_CI_AI_WS
ALTER DATABASE Products COLLATE Modern_Spanish_CI_AI_WS;

-- allow users back into the database
ALTER DATABASE Products SET MULTI_USER

Step 4

Let's check again if the change was successful:

SELECT name, collation_name FROM sys.databases WHERE name = 'Products';

SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductGuid'

Below we can see that the database collation has changed, but the column in the table was not changed.

SQL Server Service List

Change Column Collation

Note that column "guidd" in the ProductGuid table's collation settings was not changed. To change the column collation, run the script below. This would be need to be done for each column in the database that you want to change.

ALTER TABLE ProductGuid ALTER COLUMN guidd nvarchar(200) COLLATE Modern_Spanish_CI_AI_WS

Now confirm the change.

SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductGuid'

Now we can see the change has been made for the column.

SQL Server Service List

Testing Outcomes with Different SQL Server Versions

SQL Server versions used in this scenario and their results:

  • Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Express Edition with Advanced Services. (Tested and successful)
  • Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Express Edition with Advanced Services. (Tested and successful)
  • Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64) Developer Edition. (Tested and successful)
  • Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2019 (CTP3.2) - 15.0.1800.32 (X64) Express Edition. (Tested and successful)

Conclusion

This approach is good to change specific user databases and columns.

Option # 2: Undocumented approach to Change SQL Server Collation

In this option we can use this approach to change collation for System Databases, User Database and Columns.

Keep in mind this is an undocumented solution and may not be supported by Microsoft if you have issues, so please test carefully before implementing in a production environment. Be aware that a successful operation cannot be guaranteed, but it is an option to try to solve the problem.

We may make use of this technique eventually in some cases, considering its pros and cons and being prepared for surprises that may occur.

This technique should be applied right after a fresh installation of SQL Server in order to modify the collation of both the instance and its databases and objects, but we may face some problems that we will need to understand, try to fix and repeat the process.

Pros

  • Easy execution with few parameters.
  • System databases collation change.
  • User database collation change.
  • Database objects collation change.

Cons

  • Problems while running resulting in sometimes irreversible errors.
  • Possible database and object corruption.
  • If there are issues, Microsoft may not support you.

A simple way to use this technique is following a few steps that will help us to quickly solver our problem. Its recommended to create a backup of all databases (including system databases) before take administrative actions on the SQL Server instance.

Its important to ensure that there are no fixed collations inside stored procedures, triggers, etc, otherwise the command below may report problems.

Step 1

The server and master database have "Latin1_General_CI_AI" collation and we'll change it to "SQL_Latin1_General_CP1_CI_AI" for the test.

The Products database and guidd column from ProductGuid table has "Modern_Spanish_CI_AI_WS" collation and we will confirm after execution that both objects have changed collation settings.

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

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation'
SELECT name, collation_name FROM sys.databases WHERE name = 'master';
SELECT name, collation_name FROM sys.databases WHERE name = 'Products';
SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductGuid'

Below we can see the collation settings.

SQL Server Instance Collation

Step 2

We have to stop the SQL Server service for the changes to be applied. After stopping the SQL Server service, we have to open a command prompt with administrative privileges and go to the BINN directory of Microsoft SQL Server, following the example below:

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

SQL Server Binnn Directory

Step 3

After the SQL Server service is stopped, execute the command below on the server.

The parameter "-s" is necessary only if you have a named instance.

sqlservr -m -T4022 -T3659 -s"SQL2017" -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

In this example, we used two trace flags:

  • T4022: Trace Flag 4022: Bypass startup procedures in SQL Server.
  • T3569: Trace Flag 3569: Enables logging all errors to errorlog during server startup.

A lot of information will appear, but no user action is required.  When it is done, press Ctrl + C and confirm SQL Server shutdown.

SQL Server Service List

Step 4

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

SQL Server Binnn Directory
SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation'
SELECT name, collation_name FROM sys.databases WHERE name = 'Master';
SELECT name, collation_name FROM sys.databases WHERE name = 'Products';
SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ProductGuid'

Below we can see the changes.

SQL Server Binnn Directory

Testing Outcomes with Different SQL Server Versions

SQL Server versions used in this scenario and their results:

  • Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Express Edition with Advanced Services. (Tested and successful)
  • Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Express Edition with Advanced Services. (Tested and successful)
  • Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64) Developer Edition. (Tested and successful)
  • Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2019 (CTP3.2) - 15.0.1800.32 (X64) Express Edition. (Tested and successful)

Conclusion

Since this is not a documented technique, it is difficult to seek help for operation errors during execution, but by using caution in an isolated environment, it is possible to validate if the process will occur without errors, so you can consider running in a production environment, of course, always ensuring that the backup of all databases is up to date.

Option # 3: Setup with SQL Server parameters to Change SQL Server Collation

In this option we can use this approach to change collation for System Databases, but it will reset the server back as if it were a new installation.  The user databases will not be updated and they will not be attached after the process.  Also, any data added to the system databases will be reset as well.

In this last option we will use the SQL Server setup via the command prompt and pass parameters to rebuild the system databases.

Before proceeding, you should backup all user databases, script out logins, script out SQL Agent Jobs, Operators, Alerts, etc. and script out server configuration settings.

If you just installed SQL Server and noticed that the collation is not correct, this is the best approach to take right after the installation to correct the collation settings.

After performing this procedure, you will need to restore or attach the user databases and run the scripts that you created above.  To change the user database collation settings and column collation settings, follow Option #1 above.

Pros

  • Much easier and faster to rebuild system databases
  • Commands documented and supported by Microsoft

Cons

  • No action on user databases, would have to use Option 1 to make these changes
  • Possible issues connecting after execution

Step 1

Let's first understand the command and parameters needed to use this scenario.

In these two links below, Microsoft provides additional information to understand the process:

Here are some remarks about rebuilding system databases found in the first link above which we will quote here:

  • Limitations and Restrictions
  • Prerequisites

Limitations and Restrictions

When the master, model, msdb, and tempdb system databases are rebuilt, the databases are dropped and re-created in their original location. If a new collation is specified in the rebuild statement, the system databases are created using that collation setting. Any user modifications to these databases are lost. For example, you may have user-defined objects in the master database, scheduled jobs in msdb, or changes to the default database settings in the model database.

Prerequisites

Keep records about:

  • All server-wide configuration values
  • All service packs and hotfixes applied to the instance of SQL Server and the current collation and reapply these updates after rebuilding the system databases.
  • Current location of all data and log files for the system databases

Scripts required to compose the prerequisites:

SELECT * FROM sys.configurations;  

SELECT  
SERVERPROPERTY('ProductVersion ') AS ProductVersion,  
SERVERPROPERTY('ProductLevel') AS ProductLevel,  
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,  
SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,  
SERVERPROPERTY('Collation') AS Collation;  

SELECT name, physical_name AS current_file_location  
FROM sys.master_files  
WWHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

Let's look at the command that will be used:

SQL Server Binnn Directory

We have many more documented parameters, but we will know just what is needed for what we need in this scenario:

  • /QUIET or /Q: Specifies that Setup run without any user interface.
  • /ACTION=REBUILDDATABASE: Specifies that Setup re-create the system databases.
  • /INSTANCENAME=InstanceName: Is the name of the instance of SQL Server. For the default instance, enter MSSQLSERVER.
  • /SQLSYSADMINACCOUNTS=accounts: Specifies the Windows groups or individual accounts to add to the sysadmin fixed server role.
  • [ /SAPWD=StrongPassword ]: Specifies the password for the SQL Server sa account.
  • [ /SQLCOLLATION=CollationName ]: : Specifies a new server-level collation (optional).

Step 2

First let's confirm which collation we have configured for the instance and system databases:

SQL Server Binnn Directory

Step 3

Let's do the backup of database Products and detach before running the setup command.

Step 4

In this example we will change the collation settings to Modern_Spanish_CI_AI_WS with Windows Authentication mode:

The command will be:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQL2017 /SQLSYSADMINACCOUNTS=DESKTOP-2J2EKBE\wnd_rebuid  / /SAPWD= wnd_auth_sql2017 /SQLCOLLATION=Modern_Spanish_CI_AI_WS
SQL Server Binnn Directory

SQL Server Binnn Directory

Step 5

Now we can confirm how things worked. We can see all system databases have been changed.

SQL Server Binnn Directory

SQL Server versions used in this scenario and their results:

  • Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Express Edition with Advanced Services. (Tested and Failed)
  • Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Express Edition with Advanced Services. (Tested and Failed)
  • Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64) Developer Edition. (Tested and successful)
  • Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Express Edition. (Tested and successful)
  • Microsoft SQL Server 2019 (CTP3.2) - 15.0.1800.32 (X64) Express Edition. (Tested and successful)

Conclusion

We can see that system databases have had their collation changed, but this process does not change user database settings, but it is still a valid option to correct a collation configuration made in error. After this is done, you can attach or restore the databases and then follow the steps in Option 1 above.

Scripts to Check Collation Settings

Below are scripts you can use to check the collation settings for your instance, database and table columns.

-- check instance collation
SELECT convert(sysname, serverproperty(N'collation')) AS [Collation]

-- check current database collation
SELECT name, collation_name FROM sys.databases WHERE database_id = DB_ID()

-- check for table columns that do not match current database collation
SELECT DB_Name() as DatabaseName, SCHEMA_NAME(o.schema_id) as SchemaName, o.name as TableName, c.name as ColumnName, c.collation_name 
FROM sys.objects o
INNER JOIN sys.columns c on o.object_id = c.object_id
WHERE o.is_ms_shipped = 0
AND collation_name is not null
AND collation_name not in (SELECT collation_name FROM sys.databases WHERE database_id = DB_ID())

-- if for some reason you get an error about collation differences for the column query you could try this 
-- or something like this where you match the collations
SELECT DB_Name() as DatabaseName, SCHEMA_NAME(o.schema_id) as SchemaName, o.name as TableName, c.name as ColumnName, c.collation_name 
FROM sys.objects o
INNER JOIN sys.columns c on o.object_id = c.object_id
WHERE o.is_ms_shipped = 0
AND collation_name is not null
AND collation_name COLLATE SQL_Latin1_General_CP1_CI_AS not in (SELECT collation_name COLLATE SQL_Latin1_General_CP1_CI_AS 
                                                                FROM sys.databases 
                                                                WHERE database_id = DB_ID())
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Douglas P. Castilho Douglas Castilho has been a SQL Server DBA over 6 years, focuses on tuning, backup, disaster recovery, mirroring, T-SQL, PL-SQL and .NET

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2019-08-30

Comments For This Article




Thursday, September 26, 2024 - 12:55:28 PM - Iaroslav Back To Top (92532)
Thanks a lot.
It helped me to solve an issue on SQL2019.

Friday, August 16, 2024 - 8:50:39 AM - Huberto Back To Top (92456)
I successfully used option 2 with Microsoft SQL Server 2022 (RTM-CU13) (KB5036432) - 16.0.4125.3 (X64)

Thursday, April 18, 2024 - 3:27:26 AM - Haim Back To Top (92188)
Thanks the start parameter works on sql 2022

Tuesday, June 20, 2023 - 4:12:32 AM - Kieran Caulfield Back To Top (91319)
Many thanks for the detailed explanation, option 2 worked for me.
(sql server 2019)

Wednesday, March 15, 2023 - 11:36:05 AM - Michael Mertens Back To Top (91013)
I successfully used option 2 to change the collation in an SQL 2022 CU1 (16.0.4003.1) instance.
Thousand Thanks !!!

Regards
Michael

Tuesday, October 25, 2022 - 4:30:34 AM - Mohammad Reza Abbasi Back To Top (90627)
I successfully changed the server collation using the option #2.
Tested with SQL Server 2019 15.0.4223
Thank you Douglas for the share this content with all DBA's
This article saved me a lot of time, because I could change the collation without re-installing the whole server.
Kind regards, Mohammad Reza

Saturday, February 5, 2022 - 5:48:37 AM - Martin Back To Top (89756)
I successfully changed the server collation using the option #2.
Tested with SQL Server 2012 11.0 x64.
Thank you Douglas for the details instructions.
This article saved me a lot of time, because I could change the collation without re-installing the whole server.
Kind regards, Martin

Tuesday, January 18, 2022 - 8:08:25 PM - Andrejs Back To Top (89674)
Hello,

Thanks for so detailed user manual.

I have used 3rd option to change Collation, but noticed one problem - after all sys-tables were configured and user databases were reattached, when I tried to add new record with national characters (in my case those are Latvian characters), problem still was there. Only when I made new table in same user database - then Collation really started to work. Please assist, what else should have been done, to not delete old table/s and make new ones, but use still old tables, even if the new configuration is made according to the best practices (3rd choice).

Thanks,
Andrejs

Friday, December 10, 2021 - 5:34:45 AM - Taavi Tiitsmaa Back To Top (89560)
Thanks, very useful instructions for changing server instance collation. Works on MS SQL Server 2019

Monday, August 9, 2021 - 1:06:13 AM - Chandan Singh Back To Top (89102)
Nice Article , very informative .

One request , Please match the trace flag number is second solution description as in the snap, trace flag number is different whereas in description , trace flag number is different .

Thursday, May 27, 2021 - 3:33:33 AM - Улантбек Back To Top (88752)
Excellent solutions. It helped me quickly resolve big problem. My respect to author. Thanks a lot!

Wednesday, May 19, 2021 - 3:51:59 AM - Mr Maarten Hofstra Back To Top (88705)
The SQLSERV command worked! I used T3659 as in the screenshot, text says T3569. not sure which one is right.

Wednesday, December 23, 2020 - 12:47:09 PM - Jeremy Back To Top (87944)
Thanks for this. Option 3 worked for my SQL 2016. I was trying to run some C# code to build a database and it kept failing. Turns out somehow my SQL server was setup on the wrong collation and was case sensitive. Since I hadn't done anything yet, the command line option 3 fixed it for me, and I didn't care about backups.

I did run into one snag. I went to Program Files (x86) directory instead of Program Files. Got Error result: -2068643838. So just in case someone else runs into that, hope that helps.

Friday, July 31, 2020 - 4:54:40 AM - Alexander Back To Top (86222)

Thank you very much, your post is helped me. 

Tuesday, December 17, 2019 - 7:12:07 AM - Manoj Lakmal Back To Top (83445)

Thanks a lot, you saved me.

Saturday, August 17, 2019 - 4:18:01 PM - d guzman Back To Top (82093)

Articles like this can be as useful as they can be harmful.

Different collations between server, databases and even within the database columns itself can exist and unfortunately an article like this omits to telling the reader that some investigation or time should be spent trying to figure out WHY.

In some cases it is safe to just change it and not ask questions but more often then not, it is worth seeking out the reason for the discreptency.

Friday, June 7, 2019 - 3:23:26 AM - Madhu Back To Top (81362)

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

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

 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 (78710)

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 (78279)

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

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

 Thank you! It helped. 

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

 Thanks a lot!

 

Tuesday, August 7, 2018 - 1:36:29 PM - Eric Wild Back To Top (77046)

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 (76198)

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

 

https://sqlquantumleap.com/2018/06/11/changing-the-collation-of-the-instance-and-all-columns-across-all-user-databases-what-could-possibly-go-wrong/

 

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

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 (76018)

 

 Hi ,

How to change collation in clustering?

IT 

Tuesday, May 8, 2018 - 1:43:50 AM - Sagar Back To Top (75890)

what is trace flags 4022 and 3659.

Wednesday, April 4, 2018 - 10:55:28 AM - Andy Hilton Back To Top (75606)

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 (75299)

Thanks,

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 8, 2018 - 4:31:41 AM - Franco Landman Back To Top (74891)

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 7, 2017 - 3:09:06 PM - Moacir de Oliveira Back To Top (73741)

 Ola

 

Obrigado. me ajudou muito.

 

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

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 (65012)

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 1, 2017 - 12:32:02 PM - Sue Back To Top (63678)

 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 (59307)

 Hi , this post is great,  tenk soo much . 

 

 

 

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

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 (54658)

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 (51580)

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 (45066)

 

 Thaks, it is very knowledgeable...

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

 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 (43844)

 

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

Thursday, November 3, 2016 - 6:55:00 AM - N03L Back To Top (43689)

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

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

 Very helpful.   Worked first time.  Thank you.

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

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 (43545)

 Thank you So Much

 

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

Thanksssss youuu , it solve my probleme :)  

 

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

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 (43158)

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 (41487)

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 (41477)

Thank you, its worked for SQL Server 2008 R2

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

Big thanks. That's very helpful :D

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

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 (41037)

 

 Nice article. it should help to solve collation proble.

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

 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 (40925)

 

Hi Experts,

 

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

 

ThanksJC

Tuesday, March 8, 2016 - 12:09:25 PM - William Brasil Back To Top (40890)

 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 2, 2016 - 6:25:42 AM - manu Back To Top (40572)

Thaks Jason for the useful tip. 

 

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

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 (38867)

Thanks mate. Your post helped me :)

Saturday, August 1, 2015 - 11:06:01 AM - Anil Kumar Back To Top (38343)

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

Saturday, August 1, 2015 - 11:01:12 AM - Anil Kumar Back To Top (38342)

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 (36381)

@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 (36378)

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 (36312)

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 (36289)

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 (36288)

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 (36286)

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 (36285)

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 (36284)

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 (36282)

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

 














get free sql tips
agree to terms