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)

create database collationtest collate Arabic_100_CS_AI_KS_WS

go

use collationtest

create table test ( t varchar(100))

insert test (t)

select 'aaaa'

union all select 'bbbb'

union all select 'cccc'

union all select 'dddd'

create table #a (t varchar(100))

insert #a select 'aaaa' union all select  'a1234' union all select '^$&Ssss'

select *

from test t

    inner join #a a on t.t = a.t

  As the result you will have the error

(3 row(s) affected)

Msg 468, Level 16, State 9, Line 3

Cannot resolve the collation conflict between "Cyrillic_General_CI_AS"

and "Arabic_100_CS_AI_KS_WS" in the equal to operation.

  Now you need to thnk about how to resolve this to get things working. The first, most obvious but the hardest way is to add a hint in join clause.

select *

from test t

    inner join #a a on t.t = a.t collate database_default

 To not mess with confusing collation names you can always rely on database default collation which alias for collate clause is database_default.

 But this approach is good for a single query not for a huje database with thousands of stored procedures that use temporary tables.

 So you need to choose either to change server collation to support your restored database or change database collation to fit the server settings.

 Script  that returns all available collations

select * from fn_helpcollations()

 Script that returns all your databases' collations

select name, collation_name

from sys.databases

 Query that returns your sql server instance's collation

select SERVERPROPERTY('collation')

 

How to change server collation

    The most clear and easy way is to completely reinstall the sql server instance. In sql server 2000 and earlier versions there is rebuild master utility - rebuildm.exe which has very clear interface but there are many cases when this utility hangs and you lose your master database forever.

   Starting from sql server 2005 rebuild master utility is integrated into installation package. So you need to load your installation drive and run from command line the next command

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="password"

/SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=Latin1_General_CI_AS

(type this command in a single line in your cmd console)

Adjust this params for your needs - INSTANCENAME - put the instance name you want to change collation of.  INSTANCENAME=MSSQLSERVER - default instance. SAPWD - new sa password, SqlCollation - new sql server instance collation. This query may be used to rebuild master of sql server 2005 or to rebuild master of sql server 2008

Consider that after rebuild you will have a fresh sql server instance. You will need to attach or restore your databases, recreate logins and so on.

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