December 12, 2012 at 12:33 pm
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
December 12, 2012 at 4:35 pm
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.
December 14, 2012 at 9:28 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply