July 13, 2010 at 6:56 am
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
All pardon me, for the incorrect SQL Server version, it should be SQL Server 2000
Thanks,
Prasanna.
July 23, 2010 at 9:35 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy