compare tables accross databases?

So I've got two identically-structured tables in two databases. They each contain member details for two seperate online forums. I want to select all the members whose email address occurs in both tables:

select * from icna.dbo.forum_users
where exists
(select 1 from his.dbo.forum_users
where email = icna.dbo.forum_users.email)

I get an error: "Cannot resolve collation conflict for equal to operation."

I have made sure that neither table contains duplicate email addresses - the following returns no rows when run against either table:

select email from forum_users
group by email
having count(*)>1

So what is causing the collation conflict? What IS a collation conflict? Can I even compare stuff between databases?

Thanks :)Try specifying the collation name in the query. I beleive it is COLLATE followed by the name of the collation. For example :

select t.a, t2.b from server1.db.tbl t
inner join server2.db.tbl t2 on
(t.lastname = t2.lastname COLLATE LATIN_BIN_GENERAL)

I'm not sure that this is the exact syntax but it should be in BOL.

Might help...............|||Sorry, I'm a little lost. I read about collate in BOL but that left me even more mystified :) I don't want to select with an inner join. Do I? All I want is all the rows from icna.forum_users where the email crops up anywhere in his.forum_users?|||Generally, you can cast collation in a query with COLLATE <collation name>. Example:

select table1.columnA , table2.columnB
from some_table table1 another_table table2
where table1.columnA = table2.columnB
COLLATE Finnish_Swedish_CI_AS

This will cast columnB to the specified collation.|||Crikey.

I've no idea what I just did but I ran this:

select table1.email AS em1 , table2.email AS em2
from icna.dbo.forum_users table1, his.dbo.forum_users table2
where table1.email = table2.email
COLLATE Finnish_Swedish_CI_AS

and got back about 80 rows of data :) I assume that these are all the people whose email occurs in both tables - could anyone explain what, exactly, "Finnish_Swedish_CI_AS" is?|||THe collation Finnish_Swedish_CI_AS was just an example of a collation.

This very collation is the Swedish/Finnish alphabet without case sensitivity but WITH accent sensitivity. Why AS instead of AI? Because our alphabet includes letters A-Z plus , and but not W. W is just regarded as a variant of V. So, I need AS to get V and W to be regarded as different characters.