Post reply

SQL Server 2005 Linked Server to Oracle

  • August 23, 2007 at 10:43 am

    #118224

    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 23, 2007 at 4:49 pm

    #729137

    Robert,

    I have seen this before. This is a bug within Oracle. You will need to contact Oracle to get the fix for it!

    This is Bug 5043675  and the patch for it is Patch No:5043675

    If you are a subscriber to Oracle Metalink, you can get the fix from that website otherwise ring Oracle and explain the Bug & Patch Number above.

    I applied this patch and it fixed it.

    Kindest Regards,

  • August 27, 2007 at 12:00 pm

    #729609

    I just applied this bug fix to our Development environment.  It is funny to note that Oracle had us apply ODAC 10.2.0.3 which doesn't look like it updates the file that this bug fix updates.  The bug fix file was from 2/20/07 the file we had on the server was 2/9/07.  I will let you know if it fixes our problem.  Just to confirm you have seen it setup where sometime deletes work and sometimes they fail? 

  • August 27, 2007 at 1:21 pm

    #729624

    The first time the job ran after replacing the DLL with the one from the patch the job failed.  So far 3 runs 1 failure 2 success.  Guess that wasn't the fix here any other ideas?  What is the lastest verison of OraOLEDB10.dll that anyone knows of?

  • August 28, 2007 at 4:34 pm

    #729944

    You are going to have to call ORACLE and log a SR.

    Kindest Regards,

  • August 30, 2007 at 6:33 am

    #730400

    Robert,

    You may also want to check into the forums within Oracle Technet/Oracle Technology Network (OTN) as well for your issue. Membership is free, with only a free registration being required. Oracle Support does monitor the OTN forums, but may ask you to submit a TAR  (Technical Access Request) for further assistance.

    OTN Home:

    http://www.oracle.com/technology/index.html

    About Oracle Technology Network

    http://www.oracle.com/technology/about/index.html

    Going another step further, Metalink also has technical forums that are also searchable and postable with Metalink itself.

    If you do find reference to a specific file or patch to download, you will require an active Metalink account.  Barring that, as Trigger suggested you can open a TAR via Metalink or by calling Global Support services.

    U.S. Technical Support: 1-800-223-1711

    Best of luck,

     

     

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • December 23, 2010 at 7:22 am

    #1265930

    People,

    We have the same problem "Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server "XXX".".

    The problema was solved change the configuration of the linked server. The commands are:

    USE [master]

    GO

    EXEC master.dbo.sp_serveroption @server=N'PIRAMIDETESTE', @optname=N'collation compatible', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PIRAMIDETESTE', @optname=N'rpc', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PIRAMIDETESTE', @optname=N'rpc out', @optvalue=N'true'

    GO

    best regards,

    George Soares

Viewing 7 posts - 1 through 6 (of 6 total)

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