Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
venkath
Posting Yak Master
202 Posts |
 Posted - 2006-06-22 : 11:28:41
|
| Hi allHow to access oracle table using linked server..what are the parameters to be provided..Thanks |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-06-22 : 12:33:37
|
here is what you need1. install the proper ORacle client, make sure to also install Oracle ODBC and/or OLE DB driver. this might be a separate download (check technet.oracle.com for this)2. make sure the tnsnames.ora file has the proper oracle db info in it and you can connect to Oracle from your host using sqlplus.3. configure the linked server using the ODBC or OLEDB driver. You need to know databsae name (from tnsnames.ora), login and password to use. 4. Test your linked server using the following syntax:SELECT * from ORACLEDB..SCHEMA.TABLENAME Notice the two dots in the above syntax, that is important. Oracle is also case sensitive and you will need to use all uppercase with SCHEMA and TABLENAME information. The ORACLEDB info above is the name you have give your linked server, many times it corresponds to the databsename you have in your tnsnames.ora.Any selects you want to do from your linked server should probably use OPENROWSET or OPENQUERY. Look them up (or google) in books online for more information and syntax. OPENROWSET/OPENQUERY combined with using the Oracle OLEDB driver will give you the best performance on your linked server queries. After doing the above post back if you have any problems. -ec |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
Accessing oracle data using linked server