Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Linked Servers to Oracle 9i

Posted on 2005-05-02
14
Medium Priority
?
263 Views
Last Modified: 2010-03-19
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 work...lol.
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?
0
Comment
Question by:bmickey
  • 6
  • 6
  • 2
14 Comments
 
LVL 19

Expert Comment

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


Melih SARICA
0
 
LVL 12

Expert Comment

by:geotiger
ID: 13911453

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?

GT
0
 

Author Comment

by:bmickey
ID: 13911693
I followed the documentation linked below to get the linked servers to oracle to work:

http://support.microsoft.com/kb/280106/#2

They use the Microsoft OLE DB Provider for Oracle
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13911831
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..
0
 

Author Comment

by:bmickey
ID: 13912057
Non-Zero,
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?
0
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 1200 total points
ID: 13912085

oopss .. sorry i dont mean that

just recreate ur linked server again like

EXEC sp_addlinkedserver
   'LinkedServerName',
   'Oracle',
   'MSDAORA',
   'Oracle Instance Name'


0
 

Author Comment

by:bmickey
ID: 13912152
OK...here goes.....cross your fingers...lol
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13915251
:P
0
 

Author Comment

by:bmickey
ID: 13916924
Well, it ran failed faster....lol...50 minutes and 27 seconds.
I am checking the logs to try and see why
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13916969
and Dont Forget 9i is a lil bit Slower .. i donno why but it is..



0
 

Author Comment

by:bmickey
ID: 13917185
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
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13917766
Its Simple .. ur connection is lost

while DTS running
0
 
LVL 12

Assisted Solution

by:geotiger
geotiger earned 800 total points
ID: 13918080
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.

GT
0
 

Author Comment

by:bmickey
ID: 13918793
GT,
So if i read you right,
-delete the linked server i just created
- run the code in DOS
- recreate the linked server

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question