Linked Servers to Oracle 9i

G Afternoon,
I have been running a linked server connection between SQL 2000 and Oracle 8 for 2 years without a hitch.
Well, be except the registery change you do to make it
With little warning, the Oracle DBA upgraded to Orcale 9i, ever since dts's that ran 20 minutes now time out at 90 hours at times.
I have loaded the 9i drivers but I can not find any documentation to complete the changes over.
I had read that 9i drivers weren't fully supported yet bit that had changed recently.
Is there another registery change I need to do?
Who is Participating?
Melih SARICAConnect With a Mentor OwnerCommented:

oopss .. sorry i dont mean that

just recreate ur linked server again like

EXEC sp_addlinkedserver
   'Oracle Instance Name'

Melih SARICAOwnerCommented:
What kinda a Connection r u using while setting up Oracle Linked Server ?
ODBC/Oracle Provider/Ms Oracle Provider ?


I have never changed registry to make linked server to work with Oracle DBs.  You just need to make sure that the DSN is linked to the proper driver. That is you need to drop the DSN on your SQL server and re-create it with new driver (9i).

Have you asked the Oracle DBA to make sure that the tables in Oracle database have been fully indexed and performance statistics are up-to-date?

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

bmickeyAuthor Commented:
I followed the documentation linked below to get the linked servers to oracle to work:

They use the Microsoft OLE DB Provider for Oracle
Melih SARICAOwnerCommented:
delete ur linked server and create it like the example in BOL

I ve some problems like u .. with SQL server 2000 and Oracle 9i.

i did solve it with Standart oracle provider..
bmickeyAuthor Commented:
you are saying I should nuke the Linked Server object and follow the
Linked servers
   -access Oracle datavase instance?

If so, how will that effect all of my code that access Oracle with OpenQuery?
bmickeyAuthor Commented: goes.....cross your
Melih SARICAOwnerCommented:
bmickeyAuthor Commented:
Well, it ran failed minutes and 27 seconds.
I am checking the logs to try and see why
Melih SARICAOwnerCommented:
and Dont Forget 9i is a lil bit Slower .. i donno why but it is..

bmickeyAuthor Commented:
I am starting to see one of two errors on tables as they fail (they start failing at about 30 minutes in)
Crazy thing is some tables will complete after the other tables fail

Unspecific error
ORA-03114: not connected to Oracle

Connection is busy wiht the results for another command
Melih SARICAOwnerCommented:
Its Simple .. ur connection is lost

while DTS running
geotigerConnect With a Mentor Commented:
Make sure that you can connect to Oracle first

1. type 'tnsping oracle_db_alias; in DOS window

2. recreate your DSN using the new driver

3. If you get a "OK" from 1, try in Excel to see if you could pull some records from a Oracle table by following Data->Import External Data->New Database query in Excel and using the DSN.

bmickeyAuthor Commented:
So if i read you right,
-delete the linked server i just created
- run the code in DOS
- recreate the linked server

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.