Linked server

[arichie]

Can anyone point me where can I find a good architectural document on
how the Linked server works in SQL Server. I am not asking about how to
use Linked server, I am interested in know how it is defined and
architected.
Our project needs a data transfer from SQL server database to the
Oracle database, I am trying to find out whether can we use DTS or
create a linked server and execute a sql query to copy data.

Thanks in Advance.
Allen

[Simon Hayes]

I'm not really sure what you're asking for - can you be more specific?
Does the information in Books Online ("Configuring Linked Servers" has
a diagram of the architecure) not give you enough information? Both DTS
and linked servers are possible ways to get data from Oracle, but
they're very different solutions, so if you can give more details about
what you need to know and what you're trying to do, someone may be able
to point you in the right direction.

Simon

[arichie]

Thanks Simon,
We have around 20 tables to be transfered from sql server to Oracle
database. The current plan is use lined server and write a stored proc
in Oracle and grab the data using simple select into statement. This is
will happen every night and couple of tables have more than million
rows.

The question is whether linked server is reliable to do these kind of
data load use DTS. I have a very limitted knowledge in SQL Server. I am
trying to find a book/document which talks about the internal
architecture of linked server and dts. Preferably not by Microsoft.

Thanks
Allen

Simon Hayes wrote:

I'm not really sure what you're asking for - can you be more specific?
Does the information in Books Online ("Configuring Linked Servers" has
a diagram of the architecure) not give you enough information? Both DTS
and linked servers are possible ways to get data from Oracle, but
they're very different solutions, so if you can give more details about
what you need to know and what you're trying to do, someone may be able
to point you in the right direction.

Simon

[Erland Sommarskog]

arichie (ar*****@gmail.com) writes:

We have around 20 tables to be transfered from sql server to Oracle
database. The current plan is use lined server and write a stored proc
in Oracle and grab the data using simple select into statement. This is
will happen every night and couple of tables have more than million
rows.

The question is whether linked server is reliable to do these kind of
data load use DTS. I have a very limitted knowledge in SQL Server. I am
trying to find a book/document which talks about the internal
architecture of linked server and dts. Preferably not by Microsoft.

I would suspect that DTS is a better solution for this than linked
tables, if nothing else for performance. Certainly, this is precisly
the scenario for which DTS is intended. (But I have never used DTS myself.)

In the linked-server scenario, SQL Server is basically a client to the
other server that uses OLE DB to connect to the remote server. (Tip is
to use Oracle's own OLE DB Provider. Microsoft has not updated their
MSDAORA for recent Oracle releass.) Internally, SQL Server transforms
the access to client API calls. I don't think there is much literature
that discusses linked servers as such.

For DTS, please have a look at http://www.sqldts.com and
visit the group microsof.public.sqlserver.dts.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[dbahooker]

allen

so you dont trust microsoft; but you trust them for your database?

go and play with your unix and be sure to check out www.tpc.org, sql
server has taken over the (affordable) world kid

and be sure to check out www.olapreport.com to see exactly how
irrelevant Oracle is to the world these days.. MS won the OLAP battle;
it's obvious to see