How to change SQL server Collation to SQL_Latin1_General_CP1_CI_AS

How to change SQL server Collation to SQL_Latin1_General_CP1_CI_AS

For eMAM, the SQL server collation must be SQL_Latin1_General_CP1_CI_AS. 
So while installing the MS SQL server if you selected a different one, follow the below steps to change it.

  • The first thing to do is to confirm the collation you have set.
            Select SERVERPROPERTY('COLLATION');
  • Stop the SQL Server Services. 
  • Go to SQL Server Configuration Manager-> SQL Server Services ->Stop SQL server service. 
  • Run Command Prompt as Administrator and navigate to SQL Server 'Binn' directory.   
       (This is for a default instance on SQL Server 2017. If you have a named instance or a different version of SQL Server, you will need to navigate the instance folder structure for your instance)
  • Execute the below command:
        sqlservr -m -T4022 -T3659 -q"SQL_Latin1_General_CP1_CI_AS"
  •  When the script starts, you will see something like the next two screens displayed below: 
  • In the first, you can see that it says it is attempting to change the collation. In the second, just before the completion message, it states that the default collation was successfully changed. 
  • Let’s close this command prompt window and then go start SQL Server and validate the change (Select SERVERPROPERTY('COLLATION');).