Lock ORACLE table from SQL Server using a view

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Working with Oracle » Lock ORACLE table from SQL Server using a...

Lock ORACLE table from SQL Server using a view

Rate Topic

Display Mode

Topic Options

Author

Message

oscarbolso

Posted Wednesday, December 12, 2012 12:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 03, 2013 2:21 PM
Points: 2, Visits: 22

Hi. I have a view defined in SQL Server 2008 R2 and that view "is looking" a table in Oracle 10g using linked server.
I need to do this (using a SqlServer-side Store Procedure):

1- get a numerator from the view.
2- do some things affecting SQL Server tables.
3- update the numerator in the view.

This takes a small time, but I want to lock the oracle table during all my process because other programs may use the table.

So.. what I want to do is something like this:

1- lock the oracle table
2- get a numerator from the view.
3- do some things affecting SQL Server tables.
3- update the numerator in the view.
4- release the lock

I don´t know how to do steps 1 and 4.

Tks for the help

Post #1395843

Jo Pattyn

Posted Wednesday, December 12, 2012 4:35 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 1,332, Visits: 4,313

There is a command to lock an entire table LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT; , but haven't tried this from MSSQL. Perhaps through openquery.

Not sure why you need the lock, as it won't prevent querying.

Post #1395920

oscarbolso

Posted Friday, December 14, 2012 9:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 03, 2013 2:21 PM
Points: 2, Visits: 22

Yes, I don´t know how to do it... I mean, i´m not sure about the behavior of table locks if I use them with openqueries.
When I get the lock? when I release (or lost) the lock? ...if I use some code like:

openquery(linked_server,get_lock(a))
...
...
my process
...
...
openquery(linked_server,update_view(a))

I´m lost here... tks for the help Jo.

Post #1396734

« Prev Topic | Next Topic »

Permissions