Solved

MS SQL2000 Linked Server to Oracle 9i

Posted on 2004-08-11
9
2,920 Views
Last Modified: 2008-01-09
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
Comment
Question by:mbeede
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11777501
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
 

Author Comment

by:mbeede
ID: 11778580
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
 
LVL 34

Expert Comment

by:arbert
ID: 11778635
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
Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

Author Comment

by:mbeede
ID: 11778737
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
 

Author Comment

by:mbeede
ID: 11778792
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
 
LVL 34

Expert Comment

by:arbert
ID: 11779465
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
 

Author Comment

by:mbeede
ID: 11779640
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
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 11779768
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
 

Author Comment

by:mbeede
ID: 11887918
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 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