Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Changing Server Default Collation

An interesting problem develops when the standard default collation for SQL Server isn't appropriate. Working with clients here in the Czech Republic, I have to reset our lab systems server default collation to Czech_CI_AS, and I found it difficult to find out how to do this via Books Online. Since we have systems running SQL 2000, SQL 2005 and SQL 2008, I have to be able to make these changes in each version, and each version has a different method for accomplishing this.

In SQL 2000, you must run rebuildm.exe in the SQL Server binaries directory, and set the collation in the setup dialog.

In SQL 2005, the following command will reset it:

setup.exe /qb INSTANCENAME=[instance] REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=[password] SQLCOLLATION=[new collation]

In SQL 2008, the command is slightly different:

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=[instance] /SQLSYSADMINACCOUNTS=[admin account] /SAPWD=[password] /SQLCOLLATION=[new collation]

Note that you should do this before loading ANY databases or logins. If you've already done so, detach the databases and script the logins using something like sp_help_revlogin (search the Microsoft KB articles for your version). Then, after rebuilding the master database, you can reattach the databases and reload the login accounts.

Collations can be very tricky, and when you're testing application compatibility it's important that issues like this are correctly handled so your testing is valid.

Allen


Comments

Posted by ericwenger1 on 22 July 2010

Allen, when I look at the bol it talks about having to delete the user databases and doing imports and export of the data. Detaching would be much easier. Do I have to detach any other databases other than the user databases?

Posted by AllenMWhite on 22 July 2010

Detaching may be easier but it wouldn't change your data to use the new default collation.  Sorry.

Leave a Comment

Please register or log in to leave a comment.