Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Change Server Collation in SQL Server 2008 Expand / Collapse
Posted Tuesday, March 15, 2011 2:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 8, 2016 4:16 PM
Points: 29, Visits: 215
This doesn't cover the cross-instance collation issues.
Using the default configuration for Linked-Servers will give you no end of headaches.
By default, the collation is eqated to the collation of the remote server/database.
TIP: If you have a linked server, make sure it's collation is set to the local instance/database.
that way you don't care what the remote collation is (even if it changes) as all 'data' will treated as though it were the local collation.

Post #1078668
Posted Tuesday, March 15, 2011 6:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 4, 2015 4:01 AM
Points: 377, Visits: 720
UMG Developer (3/15/2011)
I think this article is a great start, but it ends too early, just using ALTER DATABASE only changes the default collation, it doesn't change existing tables, columns, or the data in the tables.

Good pick up!
Tables within each DB will not only keep their old collation for pre-existing columns, but newly created columns will have the new server collation, leading to a nightmare scenario of mixed collations within the same table.

I can contribute this script, the major part of it borrowed from another SQLServerCentral contributor (sorry I did not preserve the name!). I tested it on a few databases and think it will work but - as usual - test it first !!!!!!

ALTER PROCEDURE [dbo].[usp_CollationChangeAllTables]
--USAGE: USE THIS DB ------- very very important
--USE thisDB
-- EXEC DBA..usp_CollationChangeAllTables

--PK & FK are not character columns so they can stay
@NewCollation varchar(255)
,@Stmt nvarchar(4000)
set @NewCollation = 'Latin1_General_CI_AS' -- change this to the collation that you need
--WAS 'SQL_Latin1_General_CP1_CI_AS'

@CName varchar(255)
,@TName sysname
,@OName sysname
,@Sql varchar(8000)
,@Size int
,@Status tinyint
,@Colorder int

declare curcolumns cursor read_only forward_only local
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

begin tran
fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder
while @@FETCH_STATUS =0
set @Sql='ALTER TABLE '+@OName+' ALTER COLUMN '+@CName+' '+@TName
SET @Sql = @Sql + CASE WHEN @Size = -1 THEN + '(MAX)' ELSE isnull ('(' +convert(varchar,@Size)+')', '') END
SET @Sql = @Sql +' 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
close curcolumns
deallocate curcolumns
commit tran
END --proc
See also
(Keep in mind TheSQLGuru's warning, all these steps will change the collation for the server, the databases default, then the table defs and will guarrantee consistency for the future, but it is not clear what happens to the data already existing in each table.)
Post #1078732
Posted Thursday, March 17, 2011 12:31 AM


Group: General Forum Members
Last Login: Friday, September 19, 2014 2:16 AM
Points: 19, Visits: 40
Nice post.
Post #1079484
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse