May 15, 2003 at 2:11 pm
I am in an environment running multiple SQL Server databases on multiple servers.
I would like to run a stored procedure that retrieves data from tables on two seperate servers. One table is in an administrative DB on one server and the other is in an accounting DB on another server.
If the tables were in the same DB I could say:
SELECT Table1.GrpID,
Table1.CategoryID,
Table2.InvoiceID,
Table2.TotalCost
FROM Table1
INNER JOIN Table2
ON Table1.CategoryID = Table2.CategoryID
If they were both on the same server I could just say:
SELECT Table1.GrpID,
Table1.CategoryID,
Table2.InvoiceID,
Table2.TotalCost
FROM AdminDB.Table1
INNER JOIN AccountingDB.Table2
ON Table1.CategoryID = Table2.CategoryID
But because the tables are in separate DBs on separate servers I need to say something like:
SELECT Table1.GrpID,
Table1.CategoryID,
Table2.InvoiceID,
Table2.TotalCost
FROM \\DBServer1.1AdminDB.Table1
INNER JOIN \\DBServer2.AccountingDB.Table2
ON Table1.CategoryID = Table2.CategoryID
but the server names won't resolve in Query Analyzer as part of the name. So can this type of thing be done?
May 15, 2003 at 2:18 pm
Yes, but you have to have a link to said server. Either you could code a linked server connection or you can run (though you may have to deal with registry settings in SQL Server 2000 SP 3) ad hoc queries using OPENQUERY.
If you want the 4-part naming convention, you'll need to create linked server connections.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply