none
Change Collation

    Question

  •  

    Hello,

     

    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?

     

    Thanks,

    Dom

     

    Thursday, August 28, 2008 8:44 PM

All replies

  • Before Backup what ever collation database have also what ever setting database have will be restore as it was with previous settings.

     

    Change collation on SQL 2000

    Bakup

    Restore

    Check.

     

     

    BR
    Praveen barath

     

    Thursday, August 28, 2008 9:33 PM
  • Hello,

     

    How do I do it on SQL2000?

    Should I leave the DB attached? Should I detach it?

     

    When I try ALTER DATABASE MyDatabase COLLATE <desired collation>
    I am getting tons of errors:

    - The column ... is dependent on database collation

     

    Thanks,

    DOm

    Thursday, August 28, 2008 9:42 PM
  • Hi,

     

    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-

    http://msdn.microsoft.com/en-us/library/aa197950%28SQL.80%29.aspx

     
    For database as mentioned
    ALTER 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 Tables
    then change the collation exec
    then use the
    ALTER 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,
                @toCollation 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'
                                                                    END
    FROM   INFORMATION_SCHEMA.COLUMNS
    WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
        and  COLLATION_NAME like @fromCollation

    Ref- http://drsql.spaces.live.com/Blog/cns!80677FB08B3162E4!1333.entry

     

    For more helpful links-

    http://www.sqlservercentral.com/Forums/Topic482594-146-1.aspx

    http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3596437&SiteID=17

     

     

    BR

    Praveen barath

     

    Mark as answer if it helps.

     

     

    Friday, August 29, 2008 11:18 AM