Openquery from Sql Server 2003 to Oracle

  • gprasanna

    Valued Member

    Points: 71

    Hi All,

    I am working on a task, where I am connecting to Oracle database to update a row from SQL server 2000. I use OPENQUERY to connect and execute the update query in the Oracle database.

    UPDATE OPENQUERY(ORACLE_LINKED, 'Select attribute1 from SettingMaster Where status = 1')

    SET ATTRIBUTE1 = '25'

    I have a issue when I try to update a record, but if it is locked by other application/user for update, my query waits infinitely.

    I need a way to set a timeout here of 60 seconds, and proceed on the next record to update.

    Any ideas are highly appreciated.

    Thanks,

    Prasanna.

  • gprasanna

    Valued Member

    Points: 71

    All pardon me, for the incorrect SQL Server version, it should be SQL Server 2000

    Thanks,

    Prasanna.

  • gprasanna

    Valued Member

    Points: 71

    Hi All,

    After lot of struggle, I finally found the solution. Adding 'FOR UPDATE NOWAIT' helped really. Ofcourse it did not happen without any reference online. I found a blog written by 'Hernk' useful for the resolution. Below is the link to the blog post.

    http://hernk.wordpress.com/2006/06/21/simulating-lock-timeout/

    the solution is:

    UPDATE OPENQUERY(ORACLE_LINKED, 'Select attribute1 from SettingMaster Where status = 1 FOR UPDATE NOWAIT')

    SET ATTRIBUTE1 = '25'

    Thanks,

    Prasanna

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

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