Changing Server Default Collation

最新推荐文章于 2024-08-25 15:29:12 发布

转载最新推荐文章于 2024-08-25 15:29:12 发布 · 745 阅读

文章标签：

#collation #server #sql server #sql #microsoft #testing

SQL SERVER 专栏收录该内容

31 篇文章

订阅专栏

本文介绍了如何在不同版本的SQL Server（包括2000、2005和2008）中更改服务器的默认排序规则为捷克_CI_AS。针对每个版本提供了具体的步骤，并强调了在进行更改之前需要考虑的重要事项。

From:

http://sqlblog.com/blogs/allen_white/archive/2009/05/29/changing-server-default-collation.aspx?CommentPosted=true#commentmessage

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.