Cannot resolve collation conflict for equal to operation

Question

Cannot resolve collation conflict for equal to operation

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Ok, so then the problem is that sometimes the dbnames in 'old' and 'new' have different collations. Assuming you can't fix the root of the problem (making all collations the same), your code needs to look something like;

select *

from " + old + ".dbo.table1

left outer join

" + new + ".dbo.table2

on " + new + " .dbo.table2.id=" + old + ".dbo.table1.id + " COLLATE " + collationname

To find what collationname should be, you can query both old and new db's

select DATABASEPROPERTYEX( 'old' , 'collation' ) as 'oldCollation'

select DATABASEPROPERTYEX( 'new' , 'collation' ) as 'newCollation'

Then you can set collationname to either of those. Though if it should be 'old' or 'new' I can't tell. Not too sure it's 100% reliable to do programatically either. There is always a possiblity that the two collations aren't compatible in any case.

If you run this query in master you see what flavors of collation-related situations may arise.

select * from master..sysmessages where description like '%collation%'

Having different collations on the same server can be very tricky, and is best to avoid, if at all possible. (Don't forget that tempdb is always there, and has only one collation) Trying to build a dynamic solution in that kind of mixed environment can prove to be quite complex.

Anyway, hope it helped some?

/Kenneth

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Everything is cool, but who did tell you that collation for this patricular column is the same as default collation for the database?

Yes, typically nobody bothers to change collations after server assignes default one to newly created column, but is it something you can rely on?

_____________
Code for TallyGenerator

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

imo you should avoid mixing collations unless there is no way you can avoid it. If you have mixed collations, but there is no real need for it, it's better to sit down and fix that instead of trying to work around it with convoluted code.

..just my .02 though

/Kenneth