SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Change Server Collation in SQL Server 2008


Change Server Collation in SQL Server 2008

Author
Message
rob.lobbe-964963
rob.lobbe-964963
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 216
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.
Ol'SureHand
Ol'SureHand
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 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]
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.)
a.rajmane
a.rajmane
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 40
Nice post.

www.sqlsuperfast.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search