Unanswered: Collation problem after migration from 2000 to 2005
Hi. Wondered if I might get a bit of advice on a minor collation problem I've experienced after migrating a database form SQL Server 2000 to 2005?
Users reported an error appearing in pages in a web-based application and I traced the problem to a stored procedure that was using a temporary table. I retained the original collation - SQL_Latin1_General_CP1_CI_AS - after migration and the error showing up was 'Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.' This makes sense as I guess temporary tables are assuming tempdb's collation. Resolution in this case was fairly simple, which was to apply an explicit collation to one of the columns in the temporary table (it only had three columns, only one requiring the collation). However the longer-term question is should I: (a) resolve similar issues in the same way as and when they arise; (b) change the collation of the migrated database to that of the default for system and new databases under 2005 - i.e. Latin1_General_CI_AS; or (b) change the collation of tempdb and the other system databases to match that of the migrated databases (sounds risky)?
I would probably go for (a) or perhaps (b) but could use some expert advise on the matter.
I would try to avoid working with different collations on your server/db/objects unless absolutely necessary. One option is to script your database and all objects contained within it without specifying the collation, run the script on your 2005 server so your db and all objects pick up that servers default collation, then export the data from the 2000 db into the 2005 db. Once complete, you should be all sync'd up.
Thanks DBriles10. Is this something I can do post-migration? If so what would be the desired order of events? I'm assuming something like this:
1. script the database in its new location under 2005 without collation
2. back up and detach the database
3. run the script to create a new database
I was going to say next, restore the original database under a different name in order to be able to import data from it to the new database however wouldn't that cause conflicts with object names? I guess restoring the original database to a different server to do the data import. Any thoughts?
Your plan looks good to me. The restore of the database as a new name will not cause conflicts with object names.
If we call the existing DB with the bad collation "FixMe", you will...
1. Script "FixMe" and be sure to not include collation settings
2. Backup and detach "FixMe" >> "FixMe" no longer exists on server
3. Run the script from step 1 to recreate "FixMe" >> "FixMe" now exists with the server's default collation settings
4. Restore database from step 2 as "FixMe_Import" >> No object name conflicts as the objects reside in separate DB's and therefore are unaware of each other.
5. Import data from "FixMe_Import" to "FixMe"
For the import, I suggest setting all contraints to NoCheck, disabling all triggers and be aware of which tables have identities so you can turn identity_insert on.