Collation Changed after restore SQL7 DB into SQL2000 DB

Hi Good Day everybody.
Thanks a lot for Hari and I managed resolved the problem on restore SQL7 DB
into SQL2000 DB.
I am encountered the problem is the default collation was changed after I
restore the data from SQL7 into SQL2000. Meaning that when I resotred from
SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS" and not
"Latin1_General_CI_AS".
a) Default collation for SQL7 DB Server is:
"SQL_Latin1_General_CP1_CI_AS".
b) Default collation for SQL2000 DB Server is (this is required collation
for the new upgrade and use by application):
" Latin1_General_CI_AS" .
I am wondering whether have any impact to the database if I execute the
command below to change the collation.
ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
Please advise.
Polar Bear
=?Utf-8?B?UG9sYXIgQmVhcg==?= (PolarBear@.discussions.microsoft.com) writes:
> Hi Good Day everybody.
> Thanks a lot for Hari and I managed resolved the problem on restore SQL7
> DB into SQL2000 DB.
> I am encountered the problem is the default collation was changed after
> I restore the data from SQL7 into SQL2000. Meaning that when I resotred
> from SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS"
> and not "Latin1_General_CI_AS".
That is because the sortorder in the SQL 7 corresponds to SQL collations
in SQL 2000. There is nothing corresponding to the Windows collations in
SQL 7.

> I am wondering whether have any impact to the database if I execute the
> command below to change the collation.
> ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
The immediate impact of the change is little. New tables and columns
will use that collation, as will variables in stored procedures etc.
However, existing tables will not, but will retain the SQL collation.
Thus a query like:
SELECT ... WHERE col = @.value
could fail with a collation conflict.
Most likely you want to change the collation throughout the database.
In this case, you need to bulk out the data, build a new database
from scripts, and bulk data back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp