Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2004
    Posts
    38

    Unanswered: SQL Linked Tables

    I have two SQL Server 2005 Databases running on the same server.

    I am working in a third database that needs to contain a union query to display data from one table in each of the other databases.

    In other words, within Database 3, I need to create View C..

    This view will show takes Fields W & X from Table A in Database 1..
    and union those values with Fields Y & Z from Table B in Database 2..

    a) Does this make any sense?
    b) What is the best way to do this?

    (I searched, but anything with the phrase 'linked table' brought up results almost exclusively from the Access forums..)

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Something like:
    Code:
    CREATE VIEW C AS
    SELECT W, X
       FROM [Database 1].dbo.[Table A]
    UNION ALL 
    SELECT Y, Z
       FROM [Database 2].dbo.[Table B]
    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Note that the brackets are only there because you have spaces in your example names... The brackets only serve to delimit "naughty" names with characters like spaces that cause SQL Server heartburn.

    -PatP

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by EndBoards
    (I searched, but anything with the phrase 'linked table' brought up results almost exclusively from the Access forums..)
    That's because we call these "Linked Servers"
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by georgev
    That's because we call these "Linked Servers"
    Bzzzzzzzz ... thank you for playing George ... we have some nice parting gifts for you.

    Linked servers are connections defined for access across servers. This user has dbs on the same server instance, and merely needs to use 3 part naming as illustrated by Pat above. Linked server would require 4 part naming.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahhh burn!
    Thanks for pointing out my stupidity - I have been the weakest link...
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but georgev was correct in stating that "Linked Tables" is not a phrase or concept commonly applied to SQL Server applications. It is an Access concept.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Oct 2004
    Posts
    38
    Yeah.. the "linked server" terminology didn't really seem to fit because I'm linking two db's on the same server.. And yes.. I'm totally from an Access background so "linked tables" was the natural search term.. Newb mistakes galore..

    Anyway...

    The code above is pretty clear.. I take it that I can just formulate a "SELECT..." statement like that and this will act like a regular view, no?

    (I'm new w/SQL Server so I can't tell for sure and I don't have my laptop here to just try it..)

    As far as the spaces... Thanks for bringing that up.. One thing I'm dealing with is an ex-Access database that uses spaces in the field names. (I built it 7 years ago before I knew better..) Are there any serious issues with this other than the inconvenience of needing brackets? Going through the whole database and front end of this application would be a pretty heavy job..

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just remember not to implement it on your new system
    Spaces in field names are just a pain!

    Can you try elaborate what you mean by
    Quote Originally Posted by EndBoards
    The code above is pretty clear.. I take it that I can just formulate a "SELECT..." statement like that and this will act like a regular view, no?
    You can think of VIEWs as "stored queries". Access queries are actually views!
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Needing brackets is the only consequence I can think of to using characters that cause MS-SQL hearburn in object names.

    SQL Server treats databases on the same server as different containers, but they all contain data and they all behave pretty much the same way. Just use the "three part name" (another term you can look up for your reading pleasure) and you can refer to any object on the SQL Server that your login has permission to access.

    -PatP

  11. #11
    Join Date
    Oct 2004
    Posts
    38
    It's been a while, but this works fine. Now I have another similar/related question.

    Is there a way that I can use:
    Database1.Table1.Field1
    as a Foreign Key in
    Database2.Table2.Field2?

    Again, both databases are on the same SQL 2005 server.

    I found an old post somewhere that says that it's not and that the workaround is is to create a trigger. However, this post is from 2003 so presumably the user was working in SQL 2000.

    I'm wondering if this is possible in SQL 2005.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - still not applicable.

Posting Permissions

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