Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40

    Unanswered: Need syntax help in join from two DB

    Hi

    I have two databases on my server, I need a simple query with one join between one table from each database.
    I looked in the help of FROM clause and found the Argument "table_source" where it explains this :
    "If the table or view exists in another database on the same computer running Microsoft® SQL Server™, use a fully qualified name in the form database.owner.object_name".

    Can someone please help me fill the variants ??
    My DB name is "Forum" the owner is "DBRND\Administrator" and the table name is "TblUsers", so I tried to write in the FROM clause :
    "FROM Forum.DBRND\Administrator.TblUsers" but it doesn't work... so anyone have any idea how should it be ?

    Thanks,

    Inon.

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    select a.col, b.col
    from db1..table1 a, db2..table2 b
    where a.colX = b.colX
    etc...

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your object owner is DBRND\Administrator?

    Look at the list of tables in Enterprise Manager. They are most likely owned by dbo:

    select * FROM Forum.dbo.TblUsers


    AND PLEASE DON'T LINK TABLES IN THE WHERE CLAUSE! (My pet peeve...)

    select a.col, b.col
    from db1..table1 a
    inner join db2..table2 b on a.colX = b.colX

    Code like a pro!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    (My pet peeve...)
    Oh, oh! I had a peeve once! Everybody kept feeding it, until it almost ate me.

    I've gotten a wee bit jaded since then, but I suspect that you'd noticed.

    -PatP

  5. #5
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Thanks A LOT!

    Sorry about pushing this thread up...

    BTW, is joining from two databases on the same server is recommended ? Because I have an option to combine the two databases, so how bad is it (if at all) to leave it like it is.

    Inon.

Posting Permissions

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