SEO

#1 (**permalink**) 02-29-2008, 06:32 AM

Hi!

I've got a similar problem: whenever I try to alter the collation of a
DATABASE, using ALTER DATABASE COLLATE <collation_name, unknown until
run time>
I get Msg 5075, because there is a schema-bound view in the database.

Do you know of any way around this, short of dropping and re-creating
the view?

Thanks in advance,

Pablo Aliskevicius.

Erland Sommarskog wrote:
> Torbjorn Sjodin (torbjorn.sjodin@se.abb.com) writes:
> > I have to change collation on my SQL Server 2000, but I don't now
how
> > to do this in a proper way.
> > I now that is possible to change collation using the ALTER
statements
> > on databases as well as tables and columns. Another option is to
> > reinstall SQL Server 2000 on the server.
> > 1. If I change collation on present databases, do I also have to
> > change collation on the system databases?
> > 2. Do I havo to rebuild the master database with the new collation?
> > 3. Is there something more I have to do?
> > 4. Is it easier or safer to reinstall SQL Server 2000?
> > 5. If I reinstall the server, what is the best way to restore the
> > databases? Backup and restore? sp_detach_db? Transfer using DTS?
> >
> > It's a lot of questions, I hope someone can help me with the
answers.
>
> It depends a little what you want to acheive. But I assume that you
> want to change the collation on a server-wide basis. SQL2000 permits
> you to have databases with different collations; you can even
determine
> collation on column level. But if you have a database with a
different
> collation the default collation for the server, and you use temp
tables
> you are likely to encounter problems. So often the most practical is
> to use the same collation throughout.
>
> In such case you must rebuild the master database with the rebuildm
utility;
> read more about it in Books Online. You need to detach all databases
> first. Then you can reattch them afterwards.
>
> As for the individual databases, it may be as simple as just saying
> ALTER DATABASE, but you may get messages like this:
>
> Server: Msg 5075, Level 16, State 1, Line 1
> The object 'ckt_ap_numbers' is dependent on database collation.
>
> I have to admit that I have not played this game in full on SQL2000
yet,
> so I have no experience to talk from. But I would guess that if you
> drop all constraints and indexes that relates to character data, you
> can then change collation with ALTER DATABASE. That is, in difference
> to previous versions of SQL Server, you need to bulk out, rebuild and
> bulk back. Still of course reapplying indexes and constraints is a
bit
> of work.
>
> --
> Erland Sommarskog, Abaris AB
> sommar@algonet.se
> SQL Server MVP

#2 (**permalink**) 02-29-2008, 06:32 AM

PABLO_ALCH (pablo@lavietimetech.com) writes:
> I've got a similar problem: whenever I try to alter the collation of a
> DATABASE, using ALTER DATABASE COLLATE <collation_name, unknown until
> run time>
> I get Msg 5075, because there is a schema-bound view in the database.
>
> Do you know of any way around this, short of dropping and re-creating
> the view?

I will have to ask: what is the point of changing the database collation
until runtime?

The only thing you change is the default collation for new columns and
the collation for variables. The collation of existing columns is
unchanged.

Maybe you are aware of this, and understand how to handle this, but I
can't see any point with this. So maybe your problem is wider than just
the schemabound view.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: Changing collation