Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: Changing SQL Collation on Database

    Hi there,

    We currently install some of client's databases on SQL Server 2000 with the collation set to Latin1_General_BIN but we have one client that was installed and now running on SQL_Latin1_General_CP1_CI_AS and I was wondering if we can change the collation on the database to Latin1_General_BIN?

    Would this have any adverse affects on the DB or the data within it? Our strings are nvarchar.

    Thanks for your help

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 11
    There should be no danger to the data itself. The basic steps are these;

    1) change the database default collation (ALTER DATABASE)
    2) Drop indexes on (n)varchar columns
    3) Change collation on each individual (n)varchar column (ALTER TABLE)
    4) Rebuild all the indexes dropped in step 2.

  3. #3
    Join Date
    Jan 2004
    Thanks for the feedback. while waiting on your reply I tried the following steps from a Microsoft knowledge base which seemed to work.

    1.) Created a new DB
    2.) Created a script of all the tables and stored procedures leaving out all the triggers, constraints, primary foreign keys etc and using the Only script 7.0 compatible feature in DTS from the existing DB so not copying collations

    3.) Ran the script on the new DB to create the tables and stored procedures

    4.) Used DTS to transfer the data into the the new DB and only the data

    5.) Created scripts for all the constraints, foreign keys, primary keys, and indexes from the source database leaving the options unchecked for the create and drop object.

    6.) Ran the scripts on the new DB

    I ran a schema comparison afterwards (SQL Delta) and it seemed fine. Would there be any affect on copying over the data using DTS?



Posting Permissions

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