Like many others, I ran into collation problems migrating from 7 to 2K and I'd like to share some observations...
Installing SQL2K with default collation, then restoring an SQL7 Backup is the major cause of the problem because the SQL7 db will have char data with non-default collation.
The normal SQL2K upgrade procedure sets the new SQL2K server default of the old SQL7 collation, usually SQL_LATIN1_GEBERAL_CP1_CI_AS rather than the SQL2K default LATIN1_GENERAL_CI_AS.
If you are happy with using the old collation as the server default, then the normal upgrade is best. If you want to switch to the new default, or simply GET RID OF THE COLLATION CONFLICT if you restored from an SQL7 backup, then the following procedure will work.
1. Use the new ALTER DATABASE modify_name command to change the db name from mydb to mydb_old, say.
2. Generate scripts for all objects in mydb_old, using Enterprise Mgr.
3. Use an editor to remove every occurance of the 'COLLATION SQL_LATIN1_GENERAL_CP1_CS_AI' string.
4. Create a new db, using the original name, mydb.
5. Run the edited script in the Query Analyser in mydb.
6 Use Data Transformation Tool to copy all objects from mydb_old to mydb.