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.

ddavesp_addlinkedserver?|||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|||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?|||This looks like what I need to do as well. Joins on two tables in different DBs on different servers. Will this work?|||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.|||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?"|||Actually,

this seemed to work:

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