Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS SQL2000 Linked Server to Oracle 9i

Posted on 2004-08-11
9
Medium Priority
?
2,922 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 1500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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...
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

610 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