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

Lock ORACLE table from SQL Server using a view Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 12:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 9:36 AM
Points: 2, Visits: 24
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
Posted Wednesday, December 12, 2012 4:35 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,395, Visits: 6,615
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
Posted Friday, December 14, 2012 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 9:36 AM
Points: 2, Visits: 24
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 »

Add to briefcase

Permissions Expand / Collapse