Search code examples
oraclesql-server-2005linked-server

SQL Server linked server error message

I have a SQL Server 2005 database server set up with a linked server to an Oracle database.

When I try to run a delete or update query, I get the following error message:

Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server

The same code was working a couple hours ago while I was testing it, but suddenly it's stopped working. Has anyone seen this before and know a way to fix it/troubleshoot further?

The same queries executed in SQL Developer directly in the DB work fine. (I know it's something with the linked server, but I'm not sure what)

Any pointers would be appreciated.

Solution

  • Use EXEC('query') in following way:

    Suppose, your previous query look like this:

    UPDATE [MyOraLinkedServer]..[XYZ].[TABLE]SET [DAT_COL] = cast (getdate() as smalldatetime)

    Change the above query with following query:

    declare @NewValue smalldatetimeset 
@NewValue = cast(getdate() as smalldatetime)
exec( 'update XYZ.TABLE set DAT_COL = ?', @NewValue ) at MyOraLinkedServer

    NOTE:To make this possible you have to enable RPC on the linked server

    In this way, it may solve your problem......