SQL Server 2005 Linked Server to Oracle

Question

SQL Server 2005 Linked Server to Oracle

SAIC Dba

SSC-Addicted

Points: 457
More actions
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.

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

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

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 1

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

Robert O'Byrne SSCrazy Points: 2017 More actions · Answer 2

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?

Paul Bezanson-322376 SSC Journeyman Points: 95 More actions · Answer 3

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.