• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2935
  • Last Modified:

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.
0
mbeede
Asked:
mbeede
  • 5
  • 4
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
arbertCommented:
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now