Need to change a database collation.

Hi, i have a server running sql2000 sp3 on win2000 server.
The server has configured the sql_latin1_general_cp1_ci_as collation,
but on this server a user create a database that has the
Modern_spanish_ci_as
collation.
Every time we make a query on that database from a database that has the
server collation it reports problems with the collation.
We want to change the db collation, but we don´t know what effect does
it will cause on the data and the application.
What do you recommend to us to change the collation'
thanks a lot for your help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!MariaGuzman wrote:
> Hi, i have a server running sql2000 sp3 on win2000 server.
> The server has configured the sql_latin1_general_cp1_ci_as collation,
> but on this server a user create a database that has the
> Modern_spanish_ci_as
> collation.
> Every time we make a query on that database from a database that has
> the server collation it reports problems with the collation.
> We want to change the db collation, but we don´t know what effect does
> it will cause on the data and the application.
> What do you recommend to us to change the collation'
> thanks a lot for your help.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
You can use the COLLATE clause on an ALTER DATABASE to change the
default collation of the database:
ALTER DATABASE MyDatabase COLLATE French_CI_AS
However, altering the default collation of a database does not change
the collations of the columns in any existing user-defined tables.
You'll need to change each table with ALTER TABLE.
There are restrictions:
You cannot alter the collation of a column that is currently referenced
by:
a.. A computed column.
b.. An index.
c.. Distribution statistics, either generated automatically or by the
CREATE STATISTICS statement.
d.. A CHECK constraint.
e.. A FOREIGN KEY constraint.
If the database is small , you might have better luck creating a new
database, migrating the objects over, renaming the old database and then
renaming the new one (keep the old one around until you are sure the
migration was done correctly).
--
David Gugick
Imceda Software
www.imceda.comsql