SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Cannot resolve collation conflict for equal to OP		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

X002548
Not Just a Number

15586 Posts
Posted - 07/01/2004 :  11:05:28  Show Profile  Reply with Quote
Anyone bang into that?

RESTORED a 7.0 Datbase CP 52 to a 2k box

Built a "Privacy" db from scratch on 2k box

Looked at some table scripts and got


OHM - sql 7

SQL_Latin1_General_CP1_CI_AS NULL 

Privacy - sql 2k

Latin1_General_CI_AS NULL

[


Ran this and got the error...any help?


select * from privacy..privacy_column p inner join ohm.information_schema.tables t ON p.table_name = t.table_name


Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

Any ideas?



Brett

8-)

mr_mist
Grunnio

United Kingdom
1870 Posts
Posted - 07/01/2004 :  11:22:14  Show Profile  Visit mr_mist's Homepage  Reply with Quote
You get it because table_name is not the same collation in both databases. You can force the collation in the sql statement (in the join) to override it.



-------
Moo. :)
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 07/01/2004 :  12:40:33  Show Profile  Reply with Quote
Thanks, I think I got that....and the collation join and all

what a mess

But what bugs me is the 7.0 restore...seems to be CP 52 and the columns are collation

SQL_Latin1_General_CP1_CI_AS NULL

And the Northwind and the newly built scrambling DB are

Latin1_General_CI_AS NULL

So unless I forget, you can't restore a db to a server instance with a different CP...but it seems like 7's collation doesn't translate in to the standard 2k collation....

Is this correct?




Brett

8-)
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 07/01/2004 :  12:43:40  Show Profile  Reply with Quote
In my SQL grab-bag I've put CASTs for COLLATION on the JOINs that connect to "string" columns in System Tables. Of course I never did to start with. Just like I didn't bother too much with upper/lower case of column names in tables in system databases.

But then one day I went to a new client and discovered they set up their SQL boxes weird! In fact, it doesn't even need Weird Policy - we had some data exported into a temporary DB on BoxOne and then transfered to BoxTwo. BoxOne was case sensitive + unusual collation, 'coz the app running on it required it; and so when it the database got to BoxTwo it was still case sensitive / weird collation / la-di-da.

Had to change all my fancy scripts to cope with interrogating a SYSOBJECTS (Sorry, "sysobjects") table in the right case/collation. PITA.

You may be gone some time ...

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 07/01/2004 :  13:03:40  Show Profile  Reply with Quote
Thanks....

I think I might a platform spefic box for the process

http://www.dbforums.com/t1003271.html



Brett

8-)
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 07/02/2004 :  02:00:15  Show Profile  Reply with Quote
OK, I have read the thread.

Is it an option to migrate the data into the COLLATION that the SQL2K box is using? IMO that would be your best bet.

When we did stuff in SQL7 we ignored collation, I expect others were the same. its only when we moved it to SQL2K we realised that there was an issue. So now we set up our SQL boxes with the SQL7 default collation Hehehe Problem Solved!

But whenever there are two collations on the same box all hell breaks lose. Typically the worst is
SELECT * INTO #TEMP FROM MYTABLE
and then
...
FROM MYTABLE AAA JOIN #TEMP TTT ON TTT.MyVarchar = AAA.MyVarchar

Bang! Collations are different - DBTEMP is on the instance default, database is something different.

Even if they have scripted the CREATE TABLE #TEMP they will have had to put COLLATION statements on all varchar / text for it not to fail.

We do. Always. Without fail.

But its amazing how we never bother to test them on a SQL box with an alien COLLATION. And when we install it on a client box with an alien collation we find we missed a few!

I reckon we need a DEV box which has no tables - the tables should be on another server with a different collation. Then we'd be sure that all tables had 3 part names, and we never missed a COLLATION statement

'Kristen' COLLATE SQL_Latin1_General_CP1_CI_AS
Go to Top of Page

bisjom
Starting Member

Australia
1 Posts
Posted - 07/18/2010 :  22:05:13  Show Profile  Reply with Quote
Try this link to solve the issue:
http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000