Restoring Databases from Win2000 to Win2003/Collation

Default Collation is as follows:-

SQL Server 2000 on Windows 2000: SQL_Latin1_General_CP1_CI_AS
SQL Server 2000 on Windows Server 2003: Latin1_General_CI_AS

Databases restored from the Windows 2000 Server to Windows Server 2003 have a different collation than what the Windows Server 2003s collation is.

Does anyone know if this new collation is a permanent change for Microsoft and Windows/SQL?

Has anybody figured out a way of changing the collation of a database without having to rebuild it into a new database or by rebuilding the master?

Any other work arounds?The attached stored procedure will generate a change script that will recollate a database. Check the script comments for known limitations. You can run this script from query analyser.

Essentially this generates a change script that: drops all indexes, constraints and statistics on columns that don't have the desired target collation. Alters the columns to the new collation, alters the database to the new collation, and replaces all the indexes, constraints and statistics removed in the first place.

I'm attaching the file in the message reply window. I hope it works.

HH