Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: 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

  2. #2
    Join Date
    Jan 2002
    Location
    Ireland
    Posts
    32
    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...................

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    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?

  4. #4
    Join Date
    Jul 2002
    Posts
    229
    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.

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    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?

  6. #6
    Join Date
    Jul 2002
    Posts
    229
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •