Sometimes, it is discovered through an application error that the application database has been installed with a wrong collation method, either through ignorance or inattention. Re-installation is not an option in most instances as it may be prohibitively costly or simply impractical. This article describes a method of saving the situation and possibly, carreers! 

Briefly, the database must be 'scripted out', the offending collation method occurances in the script replaced with that required, and then the database recreated with the script, after the usual precaution of backing up the database before the undertaking.The script must NOT be executed inside Microsoft SQL Server Management Studio as the process usually times out with an error. It must be executed on the command line using sqlcmd.

Ok, here we go:

Open Microsoft SQL Server Management Studio.

1. SCRIPT THE DATABASE and DATA by right clicking on the database with the issue and select > Tasks > Generate Scripts,

on the  next screen click Next

On the next screen select "Script entire database and all database objects" Click Next

On the next screen Click “Advanced”.

On the "Advanced Scripting Options" 

• Change Script Collation: True.
• Change Type of data to script: Schema and data.
• OK to close window
• 

• Change File name to a suitable location:  C:\TEMP\ACNDBscript.sql and click Next

Click Next 

The script will be generated

2. BACKUP AND REMOVE or RENAME the target database

Backup the current target database. 

Once completed, either completely delete the existing target database, or detach it and rename its .mdf and .ldf files.

3. REPLACING THE COLLATION:

Edit the script C:\Temp\ACNDBscript.sql

• Search and replace all collation occurrences with desired collation method. e.g. Latin1_General_BIN
• Save the script.
•  

4. EXECUTE THE SQL SCRIPT THROUGH COMMAND LINE (using SQLCMD)

• Why?  Because when loading data, sqlcmd proves to be more reliable than executing the script directly in SSMS, which often times out, with an error. 

  • Start the command line in Administrator mode
  • Go to the place you saved the script: CD C:\TEMP\ACNDBcript.sql
  • Adjust the below command line to your own scenario and execute it:

  [ sqlcmd -S X3V11TRN\X3V12 -U sa -P passw0rd -i C:\Temp\ACNDBscript.sql ]

  [ sqlcmd -S SQLSERVER\INSTANCE -U SQLUSER -P SQLPASSWORD -i C:\TEMP\ACNDBscript.sql ]

  • The script will create the database and load the records one by one.

  If successful, it will normally end with the good news below:

  

And that's it. You have saved your reputation and possibly from much worse.