Monday, March 19, 2012

Oracle/MS SQL Interface

Hi everyone. I need to be able to query a database in Oracle and then
actually save the data in an MS SQL database, does anyone know what is
the best way to do this? Would it be best to do everything internal
(inside MS SQL) or external (with some script: javascript, vbscript,
perl)?

Thanks in advanced,
MikeSorry for the repeat, I didn't think my last one went through.|||brogdonm wrote:
> Hi everyone. I need to be able to query a database in Oracle and then
> actually save the data in an MS SQL database, does anyone know what is
> the best way to do this? Would it be best to do everything internal
> (inside MS SQL) or external (with some script: javascript, vbscript,
> perl)?
> Thanks in advanced,
> Mike

It depends on what additionally you want to do with the data. If you
are looking to simply replicate your Oracle data in MS SQL Server use
DTS. If you are looking to present the data, say in a web page, you
might want to use any of the various scripting languages.
You'll still end up doing quite a bit internally, probably with views
and stored procedures.
That's my guess.

Thanks|||Hi

You may want to look at using a linked server, see Books online for more
information.

John

"brogdonm" <Brogdons@.gmail.com> wrote in message
news:1135714953.066647.272150@.g43g2000cwa.googlegr oups.com...
> Hi everyone. I need to be able to query a database in Oracle and then
> actually save the data in an MS SQL database, does anyone know what is
> the best way to do this? Would it be best to do everything internal
> (inside MS SQL) or external (with some script: javascript, vbscript,
> perl)?
> Thanks in advanced,
> Mike|||I don't really know what DTS is, could you please inform me.|||brogdonm (Brogdons@.gmail.com) writes:
> I don't really know what DTS is, could you please inform me.

DTS = Data Transformation Services. This comes with SQL Server. DTS is
a tool for exporting and importing data. If you would like to drag
you entire table over to Oracle, this could be a good choice, particularly
if you are doing it on regular basis. Disclaimer: I have never used DTS
myself.

But if you want to run ad-hoc queries aginst the Oracle table, then it's
better to set up a linked server. You can then access the Oracle table
with four-part notation:

SELECT * FROM ORACLE.catalog.schema.tbl

and also join with local tables.

For information on setting up a linked server, see sp_addlinkedserver
in Books Online.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi

You may walso want to check out http://support.microsoft.com/kb/280106/

John

"brogdonm" <Brogdons@.gmail.com> wrote in message
news:1135792776.737662.236880@.o13g2000cwo.googlegr oups.com...
>I don't really know what DTS is, could you please inform me.

No comments:

Post a Comment