Change Server Collation in SQL Server 2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kevin Sermijn » Change Server Collation in SQL Server 2008

13 posts, Page 2 of 2

««1 2

Change Server Collation in SQL Server 2008

Rate Topic

Display Mode

Topic Options

Author

Message

rob.lobbe-964963

Posted Tuesday, March 15, 2011 2:40 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:38 PM
Points: 23, Visits: 177

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

Ol'SureHand

Posted Tuesday, March 15, 2011 6:36 PM

Old Hand

Group: General Forum Members
Last Login: Sunday, May 12, 2013 11:47 PM
Points: 356, Visits: 700

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]
as
--USAGE: USE THIS DB ------- very very important
--USE thisDB
-- EXEC DBA..usp_CollationChangeAllTables

begin
--ALTER TABLE dbo.tblXXX DROP CONSTRAINT
--PK & FK are not character columns so they can stay
declare
@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'

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
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
PRINT @Sql
fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder
end
close curcolumns
deallocate curcolumns
commit tran
END --proc
See also
http://www.sqlservercentral.com/Forums/Search1-0-2.aspx?SessionID=cnn0n2an3ejd3w45lgky3345&SortBy=2&SortOrder=1
(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

a.rajmane

Posted Thursday, March 17, 2011 12:31 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, April 16, 2011 10:43 PM
Points: 19, Visits: 39

Nice post.

www.sqlsuperfast.com

Post #1079484

« Prev Topic | Next Topic »

13 posts, Page 2 of 2

««1 2

Permissions