Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question

4 Answers 4

up vote 3 down vote accepted

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
share|improve this answer

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

JR

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.