Posted by Erland Sommarskog on 10/14/07 08:15

(rzito@si.rr.com) writes:
> I am working on a databse on my local box, my source data is on
> another. How can I link the database and table from one server to
> another? Currently I am using DTS to just transfer the records!

You can set up a linked server. If the stars align, it's as simple as

sp_addlinkedserver THATSERVER

then you can query the remote tables with four-part notation:

select col1, col2 from THATSERVER.db.dbo.tbl

If you are on SQL 2005, you can use synonyms to make your code cleaner:

CREATE SYNONYM remotebl FOR THATSERVER.db.dbo.tlb

and then go:

select col1, col2 FROM remotetbl

Linked servers are a bit tricky to work with, and one obstacle is to
get authentication to work. I can't say that I have fully understood the
rules, but I have not dug very hard into it. You can use
sp_addlinkedsrvlogin to specify how you are to connect to the remote server.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]