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