Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
X002548
Not Just a Number
15586 Posts |
 Posted - 2004-07-01 : 11:05:28
|
Anyone bang into that?RESTORED a 7.0 Datbase CP 52 to a 2k boxBuilt a "Privacy" db from scratch on 2k boxLooked at some table scripts and gotOHM - sql 7SQL_Latin1_General_CP1_CI_AS NULL Privacy - sql 2kLatin1_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 1Cannot resolve collation conflict for equal to operation.Any ideas?Brett8-) |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-07-01 : 11:22:14
|
| 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. :) |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-01 : 12:40:33
|
| Thanks, I think I got that....and the collation join and allwhat a messBut what bugs me is the 7.0 restore...seems to be CP 52 and the columns are collationSQL_Latin1_General_CP1_CI_AS NULLAnd the Northwind and the newly built scrambling DB areLatin1_General_CI_AS NULLSo 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? Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-01 : 12:43:40
|
| 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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-02 : 02:00:15
|
| 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 isSELECT * INTO #TEMP FROM MYTABLEand then...FROM MYTABLE AAA JOIN #TEMP TTT ON TTT.MyVarchar = AAA.MyVarcharBang! 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 |
|
|
|
bisjom
Starting Member
4 Posts |
|
|
|
|
|
|
|
Cannot resolve collation conflict for equal to OP