Post reply

SQL Server 2005 Linked Server to Oracle

  • August 23, 2007 at 10:43 am

    #120181

    Ok people might see this post and think it is the same old thing about not being to delete rows from Oracle, but it is a little more complicated than that.  Nothing I have seen posted anywhere matches what we are having an issue with. 

    We currently have a 2 node cluster running Version Microsoft SQL Server 2005 - 9.00.3042.00 (X64)   Feb 10 2007 00:59:02   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2).  As far as Oracle Client tools we have Oracle 10G installed.  We worked our way through the problems of making sure in-processing is checked and so fourth and so on.  Then we ran into the issue of not being able to delete rows from Oracle.  We thought we had solved this problem by installing ODAC version 10.2.0.3.00; however deletes only work occasionally now.  What we have done is setup 2 jobs in development which run every 30 minutes.  One job is to populate an Oracle table which has never failed.  The other job which fails about 35-40% of the time is to delete rows from that Oracle table.  Has anyone run into this intermittent delete failure? 

     Below is the error message we get when the job fails to delete.

     

    DELETE [edwdev]..SYSTEM.SAIC_ARTIFACT_LOG FROM tblArtifactLog src WHERE src.source_seq_no = SAIC_ARTIFACT_LOG.art_seq_no  and src.source_name = 'edwdev' [SQLSTATE 01000] (Message 0)  Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server "edwdev". [SQLSTATE 42000] (Error 7333).  The step failed.

  • August 24, 2007 at 8:59 pm

    #729409

    Probably the first thing I would look at is whether or not SQL Server can determine the primary key of the remote table; does the remote table have constraints, etc?  The bookmark error would seem to indicate that SQL Server just can't figure out how to tell Oracle to delete row X.

    Joe

     

  • August 27, 2007 at 8:29 am

    #729555

    If the job works once, you know there's nothing wrong with the statement, connection etc.

    I'm a novice at Oracle, but could it have something to do with table/record locks, or maybe a connection or statement timeout?

  • August 31, 2007 at 1:30 am

    #730663

    We've had problems with the OraOLEDB.Oracle provider in the past.  We switched to the MSDAORA provider and have had no difficulty since.  Just something to consider.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply