Issue Select statement to read Oracle table from SQL Server

[rmurgia]

I created an ODBC connection with the Microsoft ODBC for Oracle driver and
successfully connected to Oracle from Microsoft Access by creating a pass
through query. Can I use this connection from within SQL Server 2000 to
issue a Select statement and pull data from an Oracle table? i.e. Select *
from Oracle.dbo.<table Name>

[Delerna]

One way is to do it through DTS. Just create a connection in the DTS designer and set it up to use your ODBC.
Of course this is only useful to download/upload data to or from a SQL Server table or view

[Delerna]

I think another way would be to link to the server under security/linked servers

I think you can connect through ODBC there. I have never done it myself. Anyone?


Anyway, you can query a linked server's tables directly in query analyser,stored procs, views....

select *
from LinkedServerName.msdb.dbo.TableName
 

[rmurgia]

One way is to do it through DTS. Just create a connection in the DTS designer and set it up to use your ODBC.
Of course this is only useful to download/upload data to or from a SQL Server table or view

Thank you for the response. I was able to create a connection through DTS, however, when I set up a transform task, the system hung when I clicked on properties. I checked on the web and found someone with a similar issue, who was told to have the Oracle drivers installed in SQL Server. I checked with our DBA and he was unwilling to install the Oracle drivers in SQL Server, so I am restricted to trying to do this with an SQL Statement.