December 29, 2005 at 12:03 am
Hi all, my problem has surfaced after a migration from 7 to 2000. Old 7 environment shows server as CP1252 and the server language as English (US) this is Win NT and historical build before my time. New SQL 2000 servers are running on Win2003 and as I ensured regional settings correctly set as English(Australian) the SQL collation defaults to LATIN1_GENERAL_CI_AS.
All good so far. However all old 7 databases (backed up and restored) to 2000 environment have SQL collation assigned as SQL_LATIN1_GENERAL_CP1_CI_AS. All is appearing as it should. I know I could have recreated each database I want to migrate incl schema objects and then manually loading data for each database I am upgrading, I am happy to live with old databases having one collation, any newly created databases with the new server collation, BUT..
I have struck a problem when creating temp tables in tempdb as it is using the default server collation and the old databases are using the old SQL collation. The tables get created but a stored proc that inserts values based on a database using the SQL collation errors as follows
"Server xxxxxxx\INSTANCE002, Procedure sp_HRMFImport, Line 79 Cannot resolve collation conflict for equal to operation."
I have worked around the problem by using the collate database_default clause when creating the temp table. I need help as even after reading BOL I cannot understand why these two collations are different the sort id, the case and accent sensitivity are the same so why am I getting this error. Also what else will the apps testing uncover?? Am I better of rebuilding SQL 2000 from scratch and choosing the SQL collation to match the migrated databases? I would prefer to use the Windows (Not SQL) collation for future upgrades to 2005.
Sorry for the long post but needed to expalin issue and have really hit a brick wall with this one thats holding up a lot of migrations.
Derek
December 30, 2005 at 7:53 am
Hi Derek,
Try this: http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1616
Miguel Cecílio
January 3, 2006 at 6:25 pm
Thanks for the idea Miguel, not sure what impact this has on the data though?? I have a lot of databases that I have migrated to the 2000 environment, I would be changing each's db default then running the script against each db to change the col defaults. I still need to know what the differences between the two collations are because they 'seem' to have the same sortid, Case and accent sensitivity so why the error? What would the impact be in changing the columns as per the script? i.e would returned values (sorted sequence) change and impact end user queries?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply