Openquery from Sql Server 2003 to Oracle

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Working with Oracle » Openquery from Sql Server 2003 to Oracle

Openquery from Sql Server 2003 to Oracle

Rate Topic

Display Mode

Topic Options

Author

Message

gprasanna

Posted Tuesday, July 13, 2010 6:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, July 26, 2010 6:17 PM
Points: 3, Visits: 27

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

gprasanna

Posted Tuesday, July 13, 2010 7:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, July 26, 2010 6:17 PM
Points: 3, Visits: 27

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

Thanks,
Prasanna.

Post #951412

gprasanna

Posted Friday, July 23, 2010 9:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, July 26, 2010 6:17 PM
Points: 3, Visits: 27

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 »

Permissions