I have a situation where I have to get delta rows ( less than 1000) from a remote SQL Server at different physical location to local SQL server. I can either establish link server and use 4 part distributed query t get data OR use DTS transform task to get data. After getting data I will be using it to compare ot existing table data and inserting new rows and updating existing rows. Since this is a set-based operation with no row-by-row transformation required I can probably use either Execute SQL task of DTS OR use stored proc.

My question is, what are the pros-cons of using DTS Vs linked server distributed query and which is more practical in long run as # of rows to retrieve from remote server increase?

DTS is nice and graphical for you, it is quick, is able to perform a lot of
complex operations against your data.
Linked Servers are easy to use (Use OPENQUERY) and they do what you want.
You also do not need to call out to an external process

To use the ExecuteSQL task in DTS you would need a linked server anyway.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"DTS beginner" <anonymous (AT) discussions (DOT) microsoft.com> wrote

either establish link server and use 4 part distributed query t get data OR
use DTS transform task to get data. After getting data I will be using it to
compare ot existing table data and inserting new rows and updating existing
rows. Since this is a set-based operation with no row-by-row transformation
required I can probably use either Execute SQL task of DTS OR use stored
proc.
retrieve from remote server increase?