|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
March 3rd, 2004, 03:06 PM
|
|||
|
|||
|
Link MS SQL Server Databases
I have a single MS SQL Server that holds multiple database instances. I have a need to access the data from one database instance from within another. Is there a way to create a view/table/procedure in my source database instance that will read the data in a table from the other database instance on the same MS SQL Server?
Any help would be appreciated. Thanks, Larry 
|
|
#2
March 3rd, 2004, 04:25 PM
|
||||
|
||||
|
Yes you can. Here's a view I defined that connects to two databases
Code:
SELECT a.id,
a.company_name,
afs.total_sales,
afs.items_count
FROM dbo.ASummary afs
INNER JOIN Database2.dbo.Affiliate a
ON afs.affiliate_id = a.id
__________________
Up the Irons What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home. "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest Down with Sharon Osbourne Puzzle of the Month solved by Keath and KevinADC, superior perl programmers of the month Looking for a perl job with kick-*** programmers in a well-known NASDAQ listed tech company with branches in the US and Europe? We're hiring. PM me for details. Requirements
|
|
#3
March 3rd, 2004, 06:10 PM
|
|||
|
|||
Re: Link MS SQL Server Databases Reply to Thread
Is it possible to do this when one database is on another server? How about without a DSN defined? I've been able to connect to the external database fine but I can't seem to figure out how to use the "FROM...IN" syntax without a DSN, all the examples I find use dBase, Paradox, or a DSN...
Thanks! - Steve Yates - ...but I forgot all about the Amnesia Conference!! ~ Taglines by Taglinator ~
|
|
#4
March 3rd, 2004, 06:18 PM
|
||||
|
||||
|
Run sp_addlinkedserver (see SQL Server Books Online for details about this procedure) on one server, to tell it about the other server. Once you run it, you don't ever need to run it again. Let's say you run it on server1 and tell it about server2. Then, you can select a table on server2 from server1 using a query like this:
Code:
SELECT a.id,
a.company_name,
afs.total_sales,
afs.items_count
FROM dbo.ASummary afs
INNER JOIN Server2.Database2.dbo.Affiliate a
ON afs.affiliate_id = a.id
|
|
#5
March 4th, 2004, 11:14 AM
|
|||
|
|||
|
Quote:
That looks ideal, unfortunately I don't have the permissions to run it through Access.  - Steve Yates - It's a small world, but I wouldn't want to paint it. ~ Taglines by Taglinator ~ Last edited by SteveYates : March 4th, 2004 at 02:38 PM.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Link MS SQL Server Databases |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
