Post reply

Linked Server Issue

  • October 13, 2009 at 10:12 am

    #156312

    Hi All,

    The issue is i have to update Oracle's table from Sql Server by comparing with local Sql Server's table.

    For this purpose i have created linked server for Oracle server.

    Through the linked server i have failed to update the oracle table so i have created view(REGIS_ORA_RXL_HR_STG_EMPLOYEE SET) for that Oracle's table in SQL Server.

    After this i have written a join query on view and Sql server table for updating oracle's table.

    Code:

    UPDATE REGIS_ORA_RXL_HR_STG_EMPLOYEE SET

    REGIS_ORA_RXL_HR_STG_EMPLOYEE.INTERFACED = '',

    REGIS_ORA_RXL_HR_STG_EMPLOYEE.END_DATE = [termination date],

    REGIS_ORA_RXL_HR_STG_EMPLOYEE.INTERFACE_DATE = NULL

    FROM REGIS_ORA_RXL_HR_STG_EMPLOYEE,sql_tbl

    WHERE

    REGIS_ORA_RXL_HR_STG_EMPLOYEE.PKEY = sql_tbl.FLXID

    AND REGIS_ORA_RXL_HR_STG_EMPLOYEE.END_DATE IS NULL

    And i m facing the below error..

    Error

    Could not fetch a row using a bookmark from OLE DB provider 'MSDAORA'.

    [OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]

    OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetLocate::GetRowsByBookmark returned 0x80040e21: ].

    Please help me, it is very IMP and urgent for me.

    Thanks in advance.

  • October 13, 2009 at 12:41 pm

    #1065339

    Is your SQL box 64 bit?

    If so, try this:

    Install the Oracle ODAC (available on the Oracle Technology Network) and a patch (#5043675 available on Metalink) that fixes a bug that causes updates and deletes to fail.

    You could also try to use the OraOLEDB instead of the MSDAORA connector.

    And lastly, try running a Select statement on the Oracle box before running the update statement (it may just want to create a resultset first).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • October 20, 2009 at 8:52 am

    #1068263

    My SQLserver box is 32 bit..

    Still the issue is not resolved...

    please help me out....

  • October 20, 2009 at 8:56 am

    #1068264

    F Y I

    I have already installed the Oracle ODAC .

    and dont have permission to change linkserver provider from MSDAORA to OraOLEDB since there are so many other jobs using it.

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

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