I've noticed that the SQL Server 7.0 code page for a default installation is SQL_Latin1_General_CP1_CI_AS, but SQL Server 2000's default code page is Latin1_General_CI_AS. This change makes a significant difference when I need to restore SQL Server 7.0 databases into SQL Server 2000. How can I get around this change?
SQL Server is moving away from its legacy self-defined code page support and toward code pages that match the Windows code pages that you choose when you set Windows locale IDs. For more information about code pages, see "How Character Data Is Stored" in SQL Server 2000 Books Online (BOL). When you upgrade from SQL Server 7.0 to SQL Server 2000, by default you keep your existing sort order and code page. However, when you perform a clean installation of SQL Server 2000, you get the new sort order and code page. If you attach a database to a SQL Server 2000 instance, SQL Server keeps whatever the collation was on the database when you created it (i.e., you keep the SQL Server 7.0 code page if you attach a SQL Server 7.0 database to SQL Server 2000). Because SQL Server is moving toward the new default code page, we recommend that if you're upgrading by doing a clean installation, then attaching a database, you need to pick a collation at SQL Server 2000 installation time that matches your SQL Server 7.0 collation. You can determine this collation by performing an upgrade (e.g., an upgrade of the Pubs database) from your SQL Server 7.0 installation, then looking at the collation that results when you query the serverproperty('collation') builtin. Microsoft has made this change to reduce the scope of differing results that incompatible SQL Server and Windows code pages cause.