Collation problem with SQL 2000

I need to migrate an application from SQL 7.0 to SQL 2000. I know
that they use different collations, and this causes problems when
7.0 applications are moved to 2000. I would like to find a
permanent fix (so I'm not happy with using "COLLATE" in future
stored procedures) and I'd like to keep the default 2000 collation
(Latin1_General_CI_AS) in order to avoid problems if the
application needs to be moved to another 2000 server in the future.
So the only option is to change the collation of the restored DB.
What is the most efficient way of doing this?
Thanks,

Marco"Marco" <mdi00@.hotmail.com> wrote in message
news:1106659656.008295.140310@.c13g2000cwb.googlegr oups.com...
>I need to migrate an application from SQL 7.0 to SQL 2000. I know
> that they use different collations, and this causes problems when
> 7.0 applications are moved to 2000. I would like to find a
> permanent fix (so I'm not happy with using "COLLATE" in future
> stored procedures) and I'd like to keep the default 2000 collation
> (Latin1_General_CI_AS) in order to avoid problems if the
> application needs to be moved to another 2000 server in the future.
> So the only option is to change the collation of the restored DB.
> What is the most efficient way of doing this?
> Thanks,
> Marco

ALTER DATABASE can change a database's collation, and ALTER TABLE can change
a single column's collation. I seem to remember changing the database's
collation once and discovering that it didn't change the collation of
existing tables, only new ones, so make sure you test properly first.

Simon|||Marco (mdi00@.hotmail.com) writes:
> I need to migrate an application from SQL 7.0 to SQL 2000. I know
> that they use different collations, and this causes problems when
> 7.0 applications are moved to 2000. I would like to find a
> permanent fix (so I'm not happy with using "COLLATE" in future
> stored procedures) and I'd like to keep the default 2000 collation
> (Latin1_General_CI_AS) in order to avoid problems if the
> application needs to be moved to another 2000 server in the future.
> So the only option is to change the collation of the restored DB.
> What is the most efficient way of doing this?

You will need to bulk out the data, build the database from scripts
and reload data.

But I would only do this if this agrees with business requirements. If
you don't have an SQL 2000 installation, you can install with the
collation of your preference. If there already is an SQL 2000 instance
on the box with a different collation, one option is to install a
second instance on the box.

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

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