Is is possible to change a Cas Sensative SQL 2000 DB ?

  • Is is possible to change a Case Sensative SQL 2000 DB to be case insensative without reinstalling?

  • The SQL Collation of a database is inherited from the Server setting when the database is created.

    Option 1: You do not have to re-install SQL Server to change the SQL Collation, but you will need to rebuild the master database.

    Option 2: When you create a table object, you can specify the Collation that you want to use by specifying the 'COLLATE <SQL Collation Name>' as part of each character field, eg.

    Create Table dbo.AAA (id int, ColA varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

    In your situation, you can recreate the affected table(s) using the appropriate COLLATE option and then transfer the data across from the old table. Note that the _CI_ in the above example implies Case-Insensitive.

  • Dave,

    one thing to keep in mind when using different collations on one server is the fact that the tempDB will always have the default server collation. This can cause errors or unwanted results if char/varchar data from your userdatabase is loaded into #tables and when compared with each other.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • Check out ALTER DATABASE in the BOL. You can use that command to change the collation.

    -SQLBill

Viewing 4 posts - 1 through 3 (of 3 total)

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