Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: How Can I Link to a Table in a Different Database?

    Hi all,

    We have lookup tables we share on 6 database servers. We keep copying these very large tables to every database we use on each server, obviously a great waste of space. We don't mind copying these tables onto each of the servers but we want to be able to link to these tables from each database from within our servers. How is this done? Thanks in advance.

    ddave

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sp_addlinkedserver?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    Brett,

    The Sr. Analyst here mentioned that if we used a function to go across the network he was concerned about increased network traffic. Does what you suggest play into that concern?

    ddave

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by dolfandave
    Brett,

    The Sr. Analyst here mentioned that if we used a function to go across the network he was concerned about increased network traffic. Does what you suggest play into that concern?

    ddave

    You could always test it with one...and set up profiler...

    I guess it all depends on how the sprocs are written...

    Ask him to check out the Network when he's copying all that data to the 6 servers....I would think that's a lot of traffic...

    And what about data syncronicity? (Is that a real word?)

    I guess you could set up replication and/or build your own with triggers...

    One of the databases would need to be a publisher and the rest subscribers...

    I've never implemented a production level linked server strategy...but from my tests, I haven't seen a problem....

    6 servers...you must be getting a lot of hits a day.....

    how big are the code tables and the db in general?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    This looks like what I need to do as well. Joins on two tables in different DBs on different servers. Will this work?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I have one caution for all of this. I do not think you can set up an alias for a linked server, unless maybe in DNS. If you move a database from one server to another, you could end up with a large amount of re-coding. This may not be a problem for your shop, but it is something to watch for.

  7. #7
    Join Date
    Feb 2004
    Posts
    193
    The databases are anywhere from 400MB to 22,000 MB. I mean I look under properties and I see 400 MB, a couple at about 1,000 MB, another at 7,000 MB and so forth. It is not for a web site. It is an internal system for medical data. I would say an average table we read off of is 600,000 records with 90-110 fields. We don't have an in-house DBA as you may have guessed.

    ddave

    "6 servers...you must be getting a lot of hits a day.....

    how big are the code tables and the db in general?"

  8. #8
    Join Date
    Feb 2004
    Posts
    193
    Actually,

    this seemed to work:

    Code:
    SELECT TOP 100 *
      FROM pfc_Premera_BC_Rx..pfc pfc1 
      LEFT JOIN SQL_Server_Training_DB..pfc
        on pfc1.pfc_pkid = pfc.pfc_pkid
      ORDER BY pfc1.pfc_pkid
    ddave

Posting Permissions

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