Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    79

    Unanswered: Changing Collations for master database.

    Hi there


    I am using SQL server 2000 and
    I want to Change Server Collations from SQL_Latin1_General_CP850_BIN to
    SQL_Latin1_General_CP1_CS_AS.

    Can anybody help me in this regard.

    Rgds

    Wilson

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If you have done absolutely nothing since installing SQL Server, you can just do this:

    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.







    Otherwise, you need to look at Books Online. It's much more complicated. Here's what BOL has to say about after:





    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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Apr 2004
    Posts
    79
    Thanks for ur response.

    But can you tell me which option should I select to install SQL_Latin1_General_CP1_CS_AS.

    as I can see only one option for CS and AS and when I choose that option(Dictionary order,case-sensitive,ascent sensitive for use with 1253(Greek) character set, my collation comes out to be Latin1_General_CP1_CS_AS.
    though I am looking for SQL_Latin1_General_CP1_CS_AS.

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    They are the same thing.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Note on the below:

    CREATE TABLE test(test_collate VARCHAR(255) COLLATE Latin1_General_CP1_CS_AS)
    CREATE TABLE test(test_collate VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS)

    The first will give you an invalid collation name. The second will compile. When you select the options you mentioned above, you are creating the second example. Make sense?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29

    Alter Collation on database level

    ALTER DATABASE name_database COLLATE SQL_Latin1_General_CP1_CS_AS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •