I have a new server SQL SERVER ENTERPRISE 2005 SP2 with Collation SQL_Latin1_General_CP1_CS_AS. All databases have this collation for now.
I have to restore to this server a database coming from a SQL 2000 Server with SQL_Latin1_General_CP1_Cl_AS.
I read everywhere both master and user databases should have the same collation...
How could I change the collation on the new restored database?
On the new server? Before Restoration? After restoration (it seems it does not work I have a lot of errors even if the option is available in the properties screen dropdown list)
On an old SQL 200 Server?
Its not so simple to perform but still there are options....
If you would like to change System database collation then we need to performe Rebuild Master.
To rebuild Master-For database as mentionedALTER DATABASE MyDatabase COLLATE <desired collation>will fail on existing database where already tables and object exist on the same collation,First you need to change all tales collation and then you will be able to change the database collation.Script all Tablesthen change the collation execthen use theALTER DATABASE MyDatabase COLLATE <desired collation>There is a Script you can execute it to script all tables and then execute it, it will fail on dependent objects,Try this it might help....Declare @fromCollation sysname,
select @fromCollation = 'SQL_Latin1_General_CP1_CI_AS' --or whatever
select @toCollation = 'Latin1_General_CI_AI' --or whatever
SELECT 'ALTER TABLE ' + quotename(TABLE_NAME) +
' ALTER COLUMN ' + quotename(COLUMN_NAME) + ' ' + quotename(DATA_TYPE) +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
WHEN DATA_TYPE in ('text','ntext') then ''
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
+' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
and COLLATION_NAME like @fromCollation
For more helpful links-
Mark as answer if it helps.