Post reply

collation setting change

  • May 14, 2002 at 12:51 pm

    #57968

    Can I change the collation setting for the default instance from SQL_Latin1_General_CP1_CI_AS to

    Latin1_General_BIN ? If so how do I do that ?

    Also, what will happen with my other instances on that server if I do that ?

    I have sql server 2000

    Thanks

    Sonali

  • May 14, 2002 at 1:33 pm

    #429138

    Check the article: "Support multilingual sorting using SQL Server 2000's COLLATE keyword"

    http://www.fawcette.com/Archives/premier/mgznarch/vbpj/2000/12dec00/ss0012/ss0012.asp

  • May 14, 2002 at 1:42 pm

    #429140

    Here is an MSDN How to Article

    How to rebuild the master database (Rebuild Master utility)

    To rebuild the master database

    Shutdown Microsoft® SQL Server™ 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.

    In the Rebuild Master dialog box, click Browse.

    In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.

    Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.

    Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.

    In the Rebuild Master dialog box, click Rebuild to start the process.

    The Rebuild Master utility reinstalls the master database.

    Note To continue, you may need to stop a server that is running.

    quote:

    Can I change the collation setting for the default instance from SQL_Latin1_General_CP1_CI_AS to

    Latin1_General_BIN ? If so how do I do that ?

    Also, what will happen with my other instances on that server if I do that ?

    I have sql server 2000

    Thanks

    Sonali

  • May 14, 2002 at 1:44 pm

    #429141

    I read that article but that does not say if I can change the server collation after the installation.

    Thanks

    Sonali

  • May 14, 2002 at 2:03 pm

    #429143

    thanks, it looks scary... I will try to find out some more info. before doing this.

    Thanks

    Sonali

  • May 14, 2002 at 2:45 pm

    #429151

    This is the correct method to change the server default collation you have to rebuild the master. If you want to make it easy on yourself then you can backup and restore you current databases in most cases after the rebuild and it will retirn their original collation but here is what is giving in BOL (updated) for SQL 2000.

    quote:

    Changing Collations

    You can change the collation of a column by using the ALTER TABLE statement:

    CREATE TABLE MyTable

    (PrimaryKey int PRIMARY KEY,

    CharCol varchar(10) COLLATE French_CI_AS NOT NULL

    )

    GO

    ALTER TABLE MyTable ALTER COLUMN CharCol

    varchar(10)COLLATE Latin1_General_CI_AS NOT NULL

    GO

    You cannot alter the collation of a column that is currently referenced by:

    A computed column.

    An index.

    Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.

    A CHECK constraint.

    A FOREIGN KEY constraint.

    You can also use the COLLATE clause on an ALTER DATABASE to change the default collation of the database:

    ALTER DATABASE MyDatabase COLLATE French_CI_AS

    Altering the default collation of a database does not change the collations of the columns in any existing user-defined tables. These can be changed with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:

    The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.

    Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables to the new collation.

    All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions to the new collation.

    The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, to the new default collation.

    After a collation has been assigned to any object other than a column or database, you cannot change the collation except by dropping and re-creating the object. This can be a complex operation. To change the default collation for an instance of Microsoft® SQL Server™ 2000 you must:

    Make sure you have all of the information or scripts needed to re-create your user databases and all of the objects in them.

    Export all of your data using a tool such as bulk copy.

    Drop all of the user databases.

    Rebuild the master database specifying the new collation.

    Create all of the databases and all of the objects in them.

    Import all of your data.

    Note Instead of changing the default collation of an instance of SQL Server 2000, you can specify a default collation for each new database you create.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply