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 dont 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.codecomments.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 dont 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.codecomments.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.com