Post reply

Updating Oracle through linked server

  • August 5, 2009 at 8:34 am

    #224597

    Hi All,

    I am trying to update specific rows in our Oracle database which will be selected by a list of reference numbers i have stored in a SQL Server 2000 table. I have tried to use OPENQUERY to do this and cannot seem to get around the fact that OPENQUREY won't accept variables in it arguments. If i hard code the refernce number into the sql argument the update works fine but (as it says in BOL) it won't accept a variable concatenated into the statment.

    Here is the working OPENQUERY statment i can use to do a single update with a hard coded 'esa_id'

    Update OPENQUERY(UTEST_SRDB2_POD, 'select esa_enteredreply, esa_replystatus from capd_extendedstudapp where esa_id = ''330000095286556''')

    set esa_enteredreply = 'DIRECT_F', esa_replystatus = 'DIRECT_F'

    Failing being able to update multiple rows using OPENQUERY i tried a different approach. Using the same linked server which i can select, update, insert etc through i tried a simple select.

    SELECT * FROM UTEST_SRDB2_POD.UTEST.UNITE.CAPD_EXTENDEDSTUDAPP WHERE ESA_ID = '330000095286556'

    and i get the following error;

    Msg 7312, Level 16, State 1, Line 1

    Invalid use of schema and/or catalog for OLE DB provider 'OraOLEDB.Oracle'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

    I have double checked the obvious like correct case and table ownership and can't find anyhting wrong. having acces to do whatever i want through the OPENQUERY using the same linked server and credentials and nothing through this method is really confusing me. Anybody got any ideas??

    Thank You

    Dave Murden

  • August 24, 2009 at 11:51 pm

    #1043997

    But I use SQL.:-)

    -Bunk-beds:-P

  • August 25, 2009 at 11:05 am

    #1044363

    Msg 7312 is most of the time related to upper case/lower case Oracle side naming issue, privileges or even existance of the target table.

    Executing storedproc below returns list of all tables you can "see" in Oracle target schema:

    sp_tables_ex @table_server=MyLink, @table_schema='MySchema'

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • August 25, 2009 at 5:00 pm

    #1044599

    I often run into issues with linked servers.Sometimes a new error appears before me even when the query looks fine.Here are a few things I try before blaming my query:

    1) Uncheck the level zero property in the provider options.

    2) select * from linked_server_name..schema.object (this format also worked sometime if the user has got the default database as the db he is querying)

    3) Sometimes few data-types are not supported by MSDAORA and few are not supported by Oraoledb.So it will be wise to use the linked server with both the drivers

    but my personal favourite is openquery syntax:-)

    four-part query is useful in case where you want to execute a remote stored procedure

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

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