Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!
Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!
|||> Changing the collation on the database level affects everything beneath it - fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it - fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>
sql