Post reply

Joining across databases

  • July 17, 2003 at 5:48 am

    #101574

    Hi,

    has anyone else encountered a problem with joining two tables together across databases

    select t1.Varchar1, t2.Varchar2

    from table1 as t1, database2.dbo.table2 as t2

    where t1.Varchar1=t2.Varchar2

    It works fine on SQL Server 7, but fails on SQL Server 2000, returning the error - 'Cannot resolve collation conflict for equal to operation.'

    the code works fine on 2000 if one of the join fields is an int, so the issue seems to be because the join is using 2 varchar fields.

    thanks

    - Ad

  • July 17, 2003 at 6:03 am

    #465826

    in 2000 databases can now have their own collation orders (known as sort order in 7.0)

    this error occurs when you join character fields between 2 databases with different collation orders. have a look at collations in BOL for a detailed explanation.

    Paul

  • July 18, 2003 at 12:25 pm

    #466104

    Try a more modern syntax:

    select t1.Varchar1, t2.Varchar2

    from table1 t1

    JOIN database2.dbo.table2 t2 on t1.Varchar1=t2.Varchar2

    Signature is NULL

Viewing 3 posts - 1 through 2 (of 2 total)

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