Changing a database collation

How can I change database collation in one operation ?USE tempdb
GO
ALTER DATABASE [database name] COLLATE [desired new collation name]
GO
USE tempdb
GO
ALTER DATABASE myDB COLLATE French_CI_AS
GO
"Alur" wrote:

> How can I change database collation in one operation ?|||Why did this message appear ?
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'wd' cannot be set
to French_CI_AS.
What should I do ?
"Edgardo Valdez, MCSD, MCDBA" wrote:
> USE tempdb
> GO
> ALTER DATABASE [database name] COLLATE [desired new collation name]
> GO
> USE tempdb
> GO
> ALTER DATABASE myDB COLLATE French_CI_AS
> GO
> "Alur" wrote:
>|||This error is coming because database is in use.
Run
alter database <db name> set single_user with rollback immediate
change collation
alter database <db name> set multi_user with rollback immediate
Regards
Amish Shah|||Thank you.
"amish" wrote:

> This error is coming because database is in use.
> Run
> alter database <db name> set single_user with rollback immediate
> change collation
> alter database <db name> set multi_user with rollback immediate
> Regards
> Amish Shah
>|||ALTER DATABASE...COLLATE will change the default collation for new tables
but this will not affect existing objects. You will still need to change
the collations of existing columns, assuming that's your intent of your
question.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:07F43B1C-F722-4E27-963C-9D020FCB1491@.microsoft.com...
> How can I change database collation in one operation ?|||Thank you.
Is there any service in SQL Server 2000 or some procedure which
can change all appropiate tables columns in a database ?
Or may be you know where can I find it.
"Dan Guzman" wrote:

> ALTER DATABASE...COLLATE will change the default collation for new tables
> but this will not affect existing objects. You will still need to change
> the collations of existing columns, assuming that's your intent of your
> question.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alur" <Alur@.discussions.microsoft.com> wrote in message
> news:07F43B1C-F722-4E27-963C-9D020FCB1491@.microsoft.com...
>
>|||I would create a new database and tables with the desired collation (no
constraints and indexes), copy data, create constraints/indexes and them
rename the db. I believe there are third party utilities to facilitate this
but I haven't used them.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:3B2DFAE2-B2E4-4FFB-8817-1A1D3F1D546B@.microsoft.com...
> Thank you.
> Is there any service in SQL Server 2000 or some procedure which
> can change all appropiate tables columns in a database ?
> Or may be you know where can I find it.
> "Dan Guzman" wrote:
>|||Thank you.
"Dan Guzman" wrote:

> I would create a new database and tables with the desired collation (no
> constraints and indexes), copy data, create constraints/indexes and them
> rename the db. I believe there are third party utilities to facilitate th
is
> but I haven't used them.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alur" <Alur@.discussions.microsoft.com> wrote in message
> news:3B2DFAE2-B2E4-4FFB-8817-1A1D3F1D546B@.microsoft.com...
>
>