How to change database or server collation

How to change database collation

To change the database collation you can use alter database operator. For instance to change collation of previously created database collationtest you can run the script

alter database collationtest collate Cyrillic_General_CI_AS

Script runs for a second and does not require the database to not have active connections. this does not change collation of the objects that have already been created. It only changes default database collation which means if you create a table with character columns in the future and you don’t explicitly specify its collation, the collation will be Cyrillic_General_CI_AS (database_default). So our problem query still fails with the same error. So what we need to do? Yes, change collation of all exisiting objects(columns) in the database. Every single column collated with previous default database collation must be altered. The following script does exactly this

declare

@NewCollation varchar(255)

,@Stmt nvarchar(4000)

,@DBName sysname

set @NewCollation = ‘Cyrillic_General_CI_AS’ — change this to the collation that you need

set @DBName = DB_NAME()

declare

@CName varchar(255)

,@TName sysname

,@OName sysname

,@Sql varchar(8000)

,@Size int

,@Status tinyint

,@Colorder int

declare curcolumns cursor read_only forward_only local

for select

QUOTENAME(C.Name)

,T.Name

,QUOTENAME(U.Name) + ‘.’ +QUOTENAME(O.Name)

,C.Prec

,C.isnullable

,C.colorder

from syscolumns C

inner join systypes T on C.xtype=T.xtype

inner join sysobjects O on C.ID=O.ID

inner join sysusers u on O.uid = u.uid

where T.Name in (‘varchar’, ‘char’, ‘text’, ‘nchar’, ‘nvarchar’, ‘ntext’)

and O.xtype in (‘U’)

and C.collation != @NewCollation

and objectProperty(O.ID, ‘ismsshipped’)=0

order by 3, 1

open curcolumns

SET XACT_ABORT ON

begin tran

fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder

while @@FETCH_STATUS =0

begin

set @Sql=’ALTER TABLE ‘+@OName+’ ALTER COLUMN ‘+@CName+’ ‘+@TName+ isnull (‘(‘

+convert(varchar,@Size)+’)’, “) +’ COLLATE ‘+ @NewCollation

+’ ‘+case when @Status=1 then ‘NULL’ else ‘NOT NULL’ end

exec(@Sql) — change this to print if you need only the script, not the action

fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder

end

close curcolumns

deallocate curcolumns

commit tran

You will also need to disable replication if affected columns consist in published articles.

If your character columns are primary keys for some tables and foreign keys in others then situation gets worse. You need to drop the foreign keys , drop primary keys, change the collation and recreate the PKs and FKs.

In the end you will need to refresh the views.

declare @ViewName varchar(255), @Sql varchar(8000)

declare curviews cursor read_only forward_only local

for Select QUOTENAME(U.Name) + ‘.’ +QUOTENAME(O.Name)

from sysobjects O

inner join sysusers u on O.uid = u.uid

Where O.xtype=’V’

open curviews

fetch curviews into @ViewName

while @@FETCH_STATUS =0

begin

Set @Sql = ‘exec sp_RefreshView “‘ + @ViewName + “"

exec (@Sql)

fetch curviews into @ViewName

end

close curviews

deallocate curviews

How to change database or server collation

Moving databases across a distributed environment very often results in collation conflict. For instance you have two sql server boxes – one in China and another one in Californina. No wonder two servers use differrent collations lets say Chinese_PRC_CI_AI and Latin1_General_CI_AS. And most probably databases hosted on each server have its server collation. One day you copy database from China to your US server and restore it. Everything goes well unless you don’t use tempdb.

Tempdb has always the same collation as the sql server instance it belongs to so if you somehow sort or match character values in tempdb (join, order by clause etc) your query will fail. You can easily reproduce this collation scenario using the next query (assume your server collation is differrent from Arabic_100_CS_AI_KS_WS)

T-SQL Script

Background : SQL2000 supports collation at different levels. Collation can be based on the Windows Collation or SQL Collation. Collation can be specified at the server, database & column level. For more details, see the BOL topics on COLLATION. So this small article should give a fairly detailed view of the various options available & gotchas! COLLATION is very powerful but requires good understanding to use them efficiently. */