CODEWITHSUNDEEP
sql-servercollation
Senior Systems Engineer
Senior Systems Engineer

Reputation: 1139

SQL Server 2008 Database Collation conversion

I'd like to configure database with the exact requirement collation of "Latin1_General_CP1_CI_AS" but for some reason in SQL Server 2008, I can only select "SQL_Latin1_General_CP1_CI_AS".

How can I select the proper collation and change it from SQL_Latin1_General_CP1_CI_AS into Latin1_General_CP1_CI_AS while retaining all of the data and the settings (schema and logins, etc)?

alt text

Upvotes: 2

Views: 8534

Answers (1)

ceth
ceth

Reputation: 45295

Try to change collation from QA:

ALTER DATABASE DBNAME
COLLATE Cyrillic_General_CI_AS

You can find all possible collations by Query:

SELECT *
FROM ::fn_helpcollations()

If you have a fields in the database with diferent collation you need to convert it:

SELECT  'ALTER TABLE ['+ 
        rtrim(TABLE_NAME)+
        '] ALTER COLUMN ['+
        rtrim(COLUMN_NAME)+
        '] '+
        rtrim(DATA_TYPE)+
    CASE WHEN NOT(CHARACTER_MAXIMUM_LENGTH IS NULL) OR (CHARACTER_MAXIMUM_LENGTH=0)
        THEN '('+convert(varchar(10),CHARACTER_MAXIMUM_LENGTH)+')'
    END+
    ' COLLATE Latin1_General_CI_AS' COLLATE Latin1_General_CI_AS 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE   (TABLE_CATALOG=DB_NAME() COLLATE Latin1_General_CI_AS) AND 
        ((DATA_TYPE LIKE '%char%' COLLATE Latin1_General_CI_AS) OR (DATA_TYPE LIKE '%text%' COLLATE Latin1_General_CI_AS)) AND
        (COLLATION_NAME IS NOT NULL) AND
        (COLLATION_NAME <> 'Latin1_General_CI_AS' COLLATE Latin1_General_CI_AS) AND 
        TABLE_NAME in (SELECT o.name 
                    FROM sysobjects o 
                    WHERE     (o.xtype = 'U'))

If database collation and sql server collation are different you can have troubles with TempDb. Basically you need to run the installation again to rebuild the master database with the new collation. You cannot change the entire server's collation any other way.

Anyway I think it is easier to install new instance of MS SQL Server with new collation :)

Upvotes: 5

Related Questions