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 Master Database Collation
SQL Server DBTest Database Collation
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.
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:
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"
[-m] single user admin mode
[-T] trace flag turned on at startup
[-s] sql server instance name
[-q] new collation to be applied
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 Instance New Collation
SQL Server Master Database New Collation
SQL Server DBTest Database New Collation
- Does this tip sound interesting to you?Please check out these tips below to explore additional scenarios:
- How to change server level collation for a SQL Server Instance
- How to Change the COLLATION of a SQL Server Column
- 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.
Last Update: 2/19/2015
About the author
View all my tips
- How to change server level collation for a SQL Ser...
- Changing SQL Server Collation After Installation...
- Script and Alter SQL Server Database Objects with ...
- How column COLLATION can affect SQL Server query p...
- Create SQL Server temporary tables with the correc...
- Identify SQL Server Instance and Database Collatio...
- Handling cross database joins that have different ...
- How to Change the COLLATION of a SQL Server Column...
- Case Sensitive Search on a Case Insensitive SQL Se...
- More SQL Server DBA Tips...