SQL Server 2000 migration to SQL Server 2005 Collation Change - Method?

Hi All,

Scenario
Recently migrated a SQL 2000 database to a SQL 2005 server.
SQL 2000 Database server collation was SQL_Latin1_General_CP1_CI_AS
SQL 2005 Database server collation was Latin1_General_CI_AS (and this is the collation we want to maintain going forward)



After restoring the SQL 2000 database on SQL 2005 we observed that the database collation was SQL_Latin1_General_CP1_CI_AS. We changed the restored database collation to Latin1_General_CI_AS. We understand that this collation change will apply to newly created tables etc on the database, but that existing tables will continue to have the old 'SQL_Latin1_General_CP1_CI_AS' collation.



What is the best method to update the collation of the existing table fields?



1) Run ALTER TABLE <table name> ALTER COLUMN <colname> <datatype> COLLATE Latin1_General_CI_AS <nullability>
on each table and column.



OR



2) BCP
BCP out data
DROP TABLE
RECREATE TABLE (with new collation>
BCP in data



OR



3) Other methods?

__________________
A.Rajesh Khanna

Hi,

I guess you can make use of the 1st option. You can make use of the below link, Setting and Changing the Column Collation for more info.

Hi ,

Recently moved a SQL2000 database to SQL2005.

Collation on SQL 2000 database server was SQL_Latin1_General_CP1_CI_AS
Colaltion on SQL 2005 database server is Latin1_General_CI_AS


After restoring the SQL 2000 database on SQL 2005 we observed that the database collation was SQL_Latin1_General_CP1_CI_AS. We have altered the database collation to Latin1_General_CI_AS. We understand this will work for all new objects created on the database but existing objects (prior to the collation change) will continue to have the 'SQL_Latin1_General_CP1_CI_AS' collation.



What is the best method to change the collation of the older columns to 'Latin1_General_CI_AS' -

1) Run ALTER TABLE ALTER COLUMN <colname> <datatype> COLLATE Latin1_General_CI_AS <nullability>

on each varchar/char column



or



2) For each table

BCP out data

DROP TABLE
RECREATE TABLE (taking the new collation Latin1_General_CI_AS)

BCP in data



3) Other methods?

__________________
A.Rajesh Khanna

Hi

You can use the reliable code that a friend (Alex Baker) published at CodeProject:

SQL Server 2000 Collation Changer. (Free source code)

CodeProject: SQL Server 2000 Collation Changer. Free source code and programming help



Note: Always keep a backup of your database when you make major changes.

The database can be used when the backup is in process. If users perform INSERT, UPDATE, or DELETE statements on the database after the backup is complete, the backup will not contain these changes.

Thanks for the more information, and for more detail please log on to this site

Database Design – Sagar Information Technologies