How to access an oracle database thru SQL server 1

[isonlyme]

Hi all,

I have SQl Server 2000 and oracle 8i and i need to do queries joining records from oracle tables to sql server tables.

Is there a way to access the oracle tables thru SQl server?

I made a remote server connection to oracle and i can see the tables but i cannot access the records thru queries or cristal reports.

I'll appreciate the help on this issue.

 

[sguslan]

Well if you successfully setup a linked server to Oracle, then use the fully qualified table names as below:

Select * from linkedservername..oracleSchema.Tablename

The Oracle schema is the table owner...

To join an Oracle table to SQL Table:

Select * from
linkedservername..oracleSchema.Tablename, SqlTableName

You can specify a where clause if you need to...

An Oracle table an be accessed using OpenRowSet or OpenQuery like:

Select * from SQLtableName, Openquery(OracleLinkedServer,'select
* from OracleTable') OraTab

 

[isonlyme]

Thanks for the reply,

So to make the life easier for the users
I can create a view and use this?


Select * from SQLOwner.SQLtableName, Openquery(OracleLinkedServer,'select
* from OracleOwner.OracleTable where OracleField = 'MYDATA'') OraTable ---> this is like an AS clause, the final name of the table?

Thanks in advance

 

[sguslan]

You bet ,,,

Example:

Create View VOraSql as

Select S.ID,O.OracleField,O.Description
from SQLOwner.SQLtableName S, Openquery(OracleLinkedServer,'select
* from OracleOwner.OracleTable where OracleField = ''MYDATA''') O

 

[isonlyme]

Thanks a lot for the great help!! it works

 

[sguslan]

Glad to help !