Cpo Design

Change MSSQL Collation

Pavel Svarc Sunday, November 12, 2017 1:31 PM

Mis-matching collation settings cause issues when: 

  • Joining columns where collation setting does not match 
  • Creating new databases, as these with have the default collation setting of the server if you do not remember to specify it. 

 

If the wrong collation setting is chosen during install, it can be modified after a fashion. 

 

NB: Undertaking the following actions removes all users and detaches all existing databases. FOLLOW WITH CAUTION. 

 

  1. Open a command line prompt with admin privileges 
  2. Cd to C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016 
  3. Run the following command:   
  4. Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="<windows server>\<windows user>" /SAPWD="<password>" /SQLCOLLATION=Latin1_General_CI_AS 
  5. Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="<windows user>" /SAPWD="<password>" /SQLCOLLATION=Latin1_General_CI_AS 
  6. Re-attach databases that already have the correct collation settings 
  7. Re-add users 

Related posts

Currenlty there are no related articles.