Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Openquery from Sql Server 2003 to Oracle Expand / Collapse
Author
Message
Posted Tuesday, July 13, 2010 6:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 8:31 AM
Points: 3, Visits: 29
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.
Post #951403
Posted Tuesday, July 13, 2010 7:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 8:31 AM
Points: 3, Visits: 29
All pardon me, for the incorrect SQL Server version, it should be SQL Server 2000

Thanks,
Prasanna.
Post #951412
Posted Friday, July 23, 2010 9:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 8:31 AM
Points: 3, Visits: 29
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
Post #958049
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse