By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,645 Members | 1,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,645 IT Pros & Developers. It's quick & easy.

Issue Select statement to read Oracle table from SQL Server

P: 63
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>
Jun 15 '08 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
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
Jun 16 '08 #2

Delerna
Expert 100+
P: 1,134
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....

Expand|Select|Wrap|Line Numbers
  1. select *
  2. from LinkedServerName.msdb.dbo.TableName
  3.  
Jun 16 '08 #3

P: 63
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.
Jun 16 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.