Default Collate Codes for SQL 7 and 2000

I am in the process of building a new sql 2000 server and will be transfering
a 2G database from a sql 7 server. I have already done this in development
and come across the collate problem where the 2000 version had a different
collate code than version 7, but both were installed with the default
settings.
I would like to know that the best options are, I assume I only have 2.
1. Make sure that the 2000 server is installed with the same collate code as
the sql 7 version.
2. Change all the collate codes when the sql 7 database has been restored.
Please can someone tell me which is the best option, and why the default
collate settings for the 2 versions are different, which causes all these
problems.
Many thanks - Richard"Richard Badge" <Richard Badge@.discussions.microsoft.com> wrote in message
news:BC784CC8-6DAF-43AA-8116-D05E2D51A9BA@.microsoft.com...
> 1. Make sure that the 2000 server is installed with the same collate code
> as
> the sql 7 version.
You can do an upgrade in place and it will pick the same collation as 7.0.
> 2. Change all the collate codes when the sql 7 database has been restored.
> Please can someone tell me which is the best option,
The best is whichever your business needs say is the best. By moving to the
new collation, you will have better support for more things.
Read up on the collations and what they have to offer in the BOL. This
should help you with your decisions.
> and why the default collate settings for the 2 versions are different,
> which causes all these
> problems.
I can't speak for Microsoft, however, the new features and additions that
can be found in the newer collations give you more flexibility. If you go
all the way back to early versions of SQL Server, the default sequence was
code page 437 (US English). This was very limited in it's support for
other languages etc.
HTH
Rick Sawtell
MCT, MCSD, MCDBA