Last week we had received a change request where we need to change the collation level of System databases as one job was getting failed with error
"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.”
We tried to change the collation level of database with
"ALTER DATABASE [DBxxx] COLLATE New_Collation"
But it was getting failed with error that you can not change the collation of system databases.
We did some google and found that we need to rebuild the system databases. But just before performing this activity we got to know that there is an option through which we can changed the collation level of database without rebuilding system databases. We did the same and the change was successful.
It is undocumented startup parameter so please do it at your own risk.
Here is the steps to do the same:
1: Make backup of all the database and server objects
2: Detach all your user databases from the server.
3: Now Stop the SQL Server Service
4: Start the SQL Server Service using command Prompt. Go to the directory as below(Path can be different in you case).
If you have more than one instance of sql server then you need to specify the -s <instancename> parameter. But in case of single sql server instance you can use the below script.
For Single SQL Instance:
sqlservr -m -T4022 -T3659 -q"<New Collation Name>"
For more than one SQL Instance:
sqlservr -s instancename -m -T4022 -T3659 -q"collation-name"
Now I am going to test the same on my local system server:
The default collation level on my server is "SQL_Latin1_General_CP1_CI_AS" . See the below screen shot for same.
When we will try to change the collation level with alter command . It give throw below error.
I have taken backups of all system and user database. Also detached the user databases on this server. Now going to stop the SQL server service.
Now open cmd and run the below command:
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER1\MSSQL\Binn>> sqlservr -s mssqlserver1 -m -T4022 -T3659 -q"Japanese_BIN"
You will see below message:
Press ctrl+C to stop.
Now start the SQL server service and check the collation level again as below:
It has been changed now. Bingoooo :)
"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.”
We tried to change the collation level of database with
"ALTER DATABASE [DBxxx] COLLATE New_Collation"
But it was getting failed with error that you can not change the collation of system databases.
We did some google and found that we need to rebuild the system databases. But just before performing this activity we got to know that there is an option through which we can changed the collation level of database without rebuilding system databases. We did the same and the change was successful.
It is undocumented startup parameter so please do it at your own risk.
Here is the steps to do the same:
1: Make backup of all the database and server objects
2: Detach all your user databases from the server.
3: Now Stop the SQL Server Service
4: Start the SQL Server Service using command Prompt. Go to the directory as below(Path can be different in you case).
If you have more than one instance of sql server then you need to specify the -s <instancename> parameter. But in case of single sql server instance you can use the below script.
For Single SQL Instance:
sqlservr -m -T4022 -T3659 -q"<New Collation Name>"
For more than one SQL Instance:
sqlservr -s instancename -m -T4022 -T3659 -q"collation-name"
The default collation level on my server is "SQL_Latin1_General_CP1_CI_AS" . See the below screen shot for same.
When we will try to change the collation level with alter command . It give throw below error.
I have taken backups of all system and user database. Also detached the user databases on this server. Now going to stop the SQL server service.
Now open cmd and run the below command:
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER1\MSSQL\Binn>> sqlservr -s mssqlserver1 -m -T4022 -T3659 -q"Japanese_BIN"
You will see below message:
Press ctrl+C to stop.
Now start the SQL server service and check the collation level again as below:
It has been changed now. Bingoooo :)
Thanks!!
No comments:
Post a Comment