Post reply

Openquery from Sql Server 2003 to Oracle

  • July 13, 2010 at 6:56 am

    #237591

    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.

  • July 13, 2010 at 7:00 am

    #1191999

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

    Thanks,

    Prasanna.

  • July 23, 2010 at 9:35 am

    #1197580

    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 2 (of 2 total)

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