How to change the collation | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to change the collation

Hello Friends I want to change the collation from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1256_CI_AS I follow following steps: To rebuild system databases and specify a new system collation
Inserted the SQL Server 2005 installation media into the disk drive. Run the following command from the command prompt: start /wait <DVD Drive>setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation>
But still i am not able to change the collation from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1256_CI_AS
http://msdn2.microsoft.com/en-us/library/ms144259.aspx and choose Custom setup where you can specify such settings. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I followed the same steps after stoping all SQL Services and In Single User Mode: To rebuild system databases and specify a new system collation
Insert the SQL Server 2005 installation media into the disk drive. Run the following command from the command prompt: start /wait <CD or DVD Drive>setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation> But still not able to change system collation.Am I missing something?
Check the setup file and sql error logs…
If your installation is successful you should see the collation change but it will not change the user database it only changes system databases…
For user databases you have create the databases with new collation and copy the data… Check the following.. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=20715
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
When you try to rebuild there is not a gui option to change the collation like 2000 rebuild?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
You can run "SELECT CONVERT(char(20), SERVERPROPERTY(‘collation’));" withou quotes and see what collation you get?
If you get the new one then you server collation changed..
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
I believe it will be nice how and what steps you are following after executing that setup.exe statement. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
SELECT CONVERT(char(50), SERVERPROPERTY(‘collation’))
Returned the same (Old one) collation.
Even in properties of Master it is showing old collation.
That means changes has not been implemented in the system.
Check the %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt file, if there are any errors… To rebuild system databases and specify a new system collation
http://msdn2.microsoft.com/en-us/library/ms144259.aspx Insert the SQL Server 2005 installation media into the disk drive. Run the following command from the command prompt: Copy Code
start /wait <CD or DVD Drive>setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation>
For INSTANCENAME, use MSSQLSERVER for a default instance. For a named instance, specify the instance name. Important:
The resource database (mssqlsystemresource) is restored from SQL Server 2005 distribution media. When you rebuild the resource database from distribution media, all service packs and hotfix updates are lost, and therefore must be reapplied. Before you proceed, see the [REBUILDDATABASE] section for more information. The /qn switch suppresses all Setup dialog boxes and error messages. If the /qn switch is specified, all Setup messages, including error messages, are written to Setup log files. For more information about log files, see How to: View SQL Server 2005 Setup Log Files. The /qb switch allows display of basic Setup dialog boxes. Error messages are also displayed. Rebuilding the master database installs all system databases to their initial location. If you have moved one or more system databases to a different location, you must move the databases again. For more information about moving system databases, see Moving System Databases. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
I mean to the steps you are attempting while setting up the SQL Server again. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>