MS SQL2000 Linked Server to Oracle 9i

I have seen the following answer to a question about liked server: "If you are using Oracle 9, you have to use Oracle's driver."

Can you please provide the exact prams needed to make the connections.  Currently I am using the MSDAORA (Microsoft OLE DB) driver and I am doing inserts into oracle and the records are relatively small, but takes for ever to transfer data.  Most of the time, if it goes longer than over night, I stop the transaction from processing and restart, but is not resolving the issue.

Your assistance is greatly appreciated.
mbeedeAsked:
Who is Participating?
 
arbertConnect With a Mentor Commented:
Oh my :)  How many records are in that destination table?  What happens if you change your open query to not return rows instead of returning all the rows??

INSERT INTO OPENQUERY([TEST], 'SELECT * FROM CHECK_INFO where 1=0')
0
 
arbertCommented:
You don't have to use the oracle driver--many times the microsoft driver works better.  If you're doing LOTS of inserts, it might be VERY slow.

What exactly are you doing?  How many records?
0
 
mbeedeAuthor Commented:
We are inserting records into a production database.  Approx 171 records, into one table that takes 15 minutes and the other database 40,000 "child" records.  When run together it seems to hang, so I stop the process and try again.  I was hoping that by using the native oracle 9i driver we could enhance the speed.  I have tried several attempts at doing this, but nothing has helped improve this process.  This dataset is relatively small, so I even separated this into a cursor, but no performance boost, except now we get some records into the database.  

Hope this helps,
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
arbertCommented:
A cursor won't buy performance!  You're going to find, even with the oracle driver, you're still going to have the slowness of the linked server...

How are you doing the inserts?  TSQL?  DTS?

Couple of suggestions--try and setup a stored proc on the sql server that you can call to do the insert.  Code the Insert using OPENQUERY to use a "passthru" to Oracle.
0
 
mbeedeAuthor Commented:
Tried the cursor to loop over each parent as a test for slowness.  I am using TSQL, but am now thinking that DTS is going to be a better way to go if speed is not increasing.

I have put this into a stored procedure and kept it out using openquery and not using openquery.  Man it is still very slow, but using the openquery did speed it up for one record from 37 minutes to 34 minutes.

Any other suggestions?
0
 
mbeedeAuthor Commented:
If I were to setup the "Oracle Provider for OLE DB", what is the "provider string"

I keep getting an error with OraOLEDB.Oracle and the MSDAOra is for the Microsoft driver so I am not sure what else to use.

Thanks,
0
 
arbertCommented:
Ya, you could try the DTS route and see what you get.  I'm a bit surprised the Openquery method is soooo slow--can you post your code?

What error do you get with the Ora driver?
0
 
mbeedeAuthor Commented:
Oracle driver -- when a new "linked server" is built using "Oracle Provider for OLE DB" the error is:

"Error 7311: Could not obtain the schema rowset for OLE DB provider 'Unknown'. The provider support the interface, but returns a failure code when it is used.

OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBschemaRowset::GetRowset returned 0x80040e37: ].


********
Here is my “Modified” code  (non specific to industry)

CREATE PROCEDURE [sp_LoadOracleData] AS

DECLARE @numCheckInfoID varchar(30)
DECLARE @TotalCount as real
Set @TotalCount = 0
SET @numCheckInfoID = ''
DECLARE curCheck CURSOR FOR
     SELECT Field1
     FROM Check_Info
     WHERE Field2 >= '7/21/2004' AND Field2 < 100

     OPEN curCheck
     FETCH NEXT FROM curCheck INTO @numCheckInfoID

     -- Loop through all checks
     WHILE @@FETCH_STATUS = 0
     BEGIN

      -- insert check summary information
        INSERT INTO OPENQUERY([TEST], 'SELECT * FROM CHECK_INFO')
      (Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13)
      SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13
      FROM Check_Info
      WHERE (Field1 = @numCheckInfoID)
      
      -- update the check info
      Update Check_Info
      SET Field12 = 1,
            Field13 = getdate()
      WHERE (Field1 = @numCheckInfoID)

      
      -- insert check detail information
      INSERT INTO OPENQUERY([TEST], 'SELECT * FROM DETAIL_INFO')
        (Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13)
      SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13
      FROM DETAIL_INFO
      WHERE (Field1 = @numCheckInfoID)

      -- update the claim info
      Update DETAIL_INFO
      SET Field12 = 1,
            Field13 = getdate()
      WHERE (Field1 = @numCheckInfoID)


      -- move to next checkID      
      FETCH NEXT FROM curCheck INTO @numCheckInfoID

     END
     CLOSE curCheck
     DEALLOCATE curCheck


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
0
 
mbeedeAuthor Commented:
65000 records.  Changing the where didn't help.  We decided to go a different route using DTS and BulkLoad on Oracle.

Thanks,
0
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.