March 28, 2002 at 5:26 pm
After moving from SQL7 to SQL2000 ( restored backup file), get error
"Cannot resolve collation conflict for equal to operation". It comes while executing many of my stored procedures. I tried to set Compatibility level to 70 and it didn't help.
I seem to have the latest SQL update 8.00.534, Developer edition on windows NT5 ( build 2195: SP2). The code line referenced doesn't help me at all. Needless to say that I didn't have these problems on SQL7. MS KB doesn't have applicable articles or saying that they're fixed in SP2 or earlier.
Any suggestions?
Thanks!
March 29, 2002 at 10:07 am
You can set the collation at the database level in 2000, run sp_sortorder to see what ytour current collation is on the SQL 7 box and make sure your db in 2000 is the same.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 29, 2002 at 11:02 am
My SQL 7 DB returns
Unicode data sorting
Locale ID = 1033
case insensitive, kana type insensitive, width insensitive
Sort Description Order
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with several Western-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
My SQL 2000 returns:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 53 on Code Page 1252 for non-Unicode Data.
So what can I do to change it? Is any problems can arise with running queries and comparing non-unicode data and unicode data?
I hope I don't have to go recompile hundreds of sprocs.
March 31, 2002 at 6:34 am
We've had the same problem and the reason for the "error" was that the SQL Server 7 database that was moved had a different sortorder/collation than the SQL Server 2000 default collation. Even if you can change collation on a database level (or even column level) in SQL Server 2000 the system databases (master,msdb,tempdb,model and distribution) still have the collation you specified when you installed the SQL 2000 machine.
So in other words if you create a #temp table it could have a different collation than the tables in your user database. Thus you have the risk that equal operations (or sort operations) made against your #temp tables will fail or produce a sort result you didn't expect. This was the case when we had this problem.
There are two ways to get around it.
1.
Change the collation on your user database so that it matches the collation that is default on the SQL 2000 Server machine. Make sure to verify all columns in all tables individually because even if you change the collation on the database the columns will still keep the old one (if I remember correctly). Your collation change will only effect new tables you create.
2.
Rewrite your code so that when you create #temp tables you specify what collation the columns in the #temp table should have. (They should have same collation as your user database).
/Argyle
Edited by - Argyle on 03/31/2002 06:39:24 AM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply