Solved

MS SQL2000 Linked Server to Oracle 9i

Posted on 2004-08-11
9
2,915 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
  • 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
Read about achieving the basic levels of HRIS security in the workplace.
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now