How to convert an MS SQL 2005 DB from Collation SQL_Latin1_General_CP1_CI to SQL_Latin1_General_CP1_C1_AS?

Question

How can I convert an MS SQL 2005 DB from collation SQL_Latin1_General_CP1_CI to SQL_Latin1_General_CP1_C1_AS?

SQL_Latin1_General_CP1_C1_AS does not appear in the list of options when I go to:

• Right click on the DB and select 'Properties'
• Options
• Collation

Answer 1

I think the collation you're looking for is CI, with the letter I, not the number 1. Check the screen again, but if you still don't see it, try running this script with your database name in it:

USE [master]
GO
ALTER DATABASE [MyDatabaseName] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Answer 2

Keep in mind that altering the collation of a database do not change the collation of existing data, but only the collation used to create new tables or columns, existing data will remain with the same collation.

If you want to fully migrate data and change collation you need to copy all data in empty tables with the right collation.

Answer 3

Have a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;325335 and see if that helps.

JR

Answer 4

AFAIK data doesn't have a collation, only the schema does. The collation determines how string values are compared. Changing the schema across all column, across all tables is a major PITA. The last time I did it I dumped the schema out to files using Red Gate SQL Compare. I then did a FIND+Replace through the files changing the schema strings. Finally I sync'd the schema back from the files to the database