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!

(rzito [at] 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 [at] sommarskog.se

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

I actually found a much easier way, I connected to my SQL database
using MS Access ADP project, and just clicked on the link and the
wizard walked me through it, thanks for the help!