Accessing Oracle Tables From Within SQL Server 2000

[Mark D Powell]

We are running SQL Server 2000 SP3. We have linked servers in use that
we use to access Oracle tables.

Recently the claim has been made that you can access Oracle tables from
within SQL Server without using a Linked Server. I searched Books
Online using keywords: linked, remote, and Oracle and did not find
anything.

A search of the newsgroup archives found only entries related to using
Linked Servers.

Is there any such method as claimed? I do not think there is, but I
need to find some support for my position or else learn something new.

Thank you
-- Mark D Powell --

[Martijn Tonies]

Re: Accessing Oracle Tables From Within SQL Server 2000

[color=blue]
> Is there any such method as claimed? I do not think there is, but I[/color]

Me neither.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions

http://www.upscene.com

My thoughts:

http://blog.upscene.com/martijn/

Database development questions? Check the forum!

http://www.databasedevelopmentforum.com

[color=blue]
> need to find some support for my position or else learn something new.
>
> Thank you
> -- Mark D Powell --
>[/color]

[DickChristoph]

Re: Accessing Oracle Tables From Within SQL Server 2000

Hi Mark

Check into OpenRowSet. I haven't done it with Oracle but I have with AS/400
DB2 files.

I could run a SQL Select Statement on the DB2 file and then insert the data
into a SQL Server table in a SQL Stored Proc.
--

-Dick Christoph
"Mark D Powell" <Mark.Powell@ed s.com> wrote in message
news:1143219908 .508356.128400@ g10g2000cwb.goo glegroups.com.. .[color=blue]
> We are running SQL Server 2000 SP3. We have linked servers in use that
> we use to access Oracle tables.
>
> Recently the claim has been made that you can access Oracle tables from
> within SQL Server without using a Linked Server. I searched Books
> Online using keywords: linked, remote, and Oracle and did not find
> anything.
>
> A search of the newsgroup archives found only entries related to using
> Linked Servers.
>
> Is there any such method as claimed? I do not think there is, but I
> need to find some support for my position or else learn something new.
>
> Thank you
> -- Mark D Powell --
>[/color]

[Mark D Powell]

Re: Accessing Oracle Tables From Within SQL Server 2000

Dick, I think you misread my post. We can select, insert, etc... to
Oracle tables from SQL Server databases. We use a Linked Database to
do it. The question is, is there any other MS provided method to
access Oracle from within a SQL Server database without using a Linked
Server? I do not think there is.

-- Mark D Powell --

[DM Unseen]

Re: Accessing Oracle Tables From Within SQL Server 2000

Depends on how you read the question. if you mean connecting without an
OLEDB connection I think the answer is "no", but else Dicks' post is
correct.

OPENROWSET: you can use to connect to external data without a linked
server, the connection properties are embedded in the OPENROWSET
call(See BOL). Having said this, the command has more limitations than
linked servers.The only advantage is using it for ad hoc connections.
And even that you can simulate by adding linked servers at runtime. I
would use Linked servers with e.g. OPENQUERY over OPENROWSET anytime.

DM Unseen