Interrogating lock information...

I'm working on a PB / OLE DB / SQL2000 application architecture. Here is my requirement:

I need to find out if/how I can interrogate a SQL2000 database for its locking information, with a fairly high degree of granularity. That is to say I'd want to extract the following info:

TABLE LOCK:
--> The name of the table thats locked. (this I can get from SYS tables...)

PAGE LOCK:
--> The table and the row or range of rows that are locked.

ROW LOCK:
--> The table and the row (RId or physical key) that is locked.

My understanding is that rows that are locked as part of a page lock, or a row that is rowlocked, is managed internally within the DBMS. Any one of the three lock types will register e.g. on the SYSLOCKS table, but only specifies the name of the locked object (i.e. table name, index, etc...).

Is my only real solution to create my own custom locking info table(s)? I don't strongly favor that sort of solution, because the code to properly manage such a table would be pretty ambitious to write, and prone to corruption at some point no matter how well we build it.

Are there any inventive solutions out there?All the tools to do that are at your finguretips, - PerfMon, DBCC, Profiler, and QA. Look into sp_lock stored procedure that will point you to master.dbo.syslocks and master.dbo.syslockinfo. Look into perfmon counters as well as various profiler templates.

Interrogate stored procedure

Hi all,
Scratching my head and wondering if this is even possible, so thought
I'd ask the collective for some advice before I get started.
I've inherited a stored procedure which contains lots (over 5000 lines)
of calculations and I'd like to be able to work from it, a calculation
and the constituent parts. For example, say the sproc contains -
DECLARE @.a
DECLARE @.b
DECLARE @.c
DECLARE @.d
DECLARE @.e
select @.b=b, @.d=d, @.e=e from mynumbers where type = 1
select @.c = @.d+@.e
select @.a = @.b * @.c
Update mynumbers SET a=@.a, c=@.c where type = 1
Then I'd like to be able to write something, so that I can simply ask
how the sproc calculates a and I would get back -
a = b * c
c = d + e
Does that make sense? Any ideas what would be the easiest way to do
this?
Thanks in advance,
BradBrad,
You could try something like the below.
hth
Ion
/*
* shows assignments using SET in a given procedure.
* assumes that all such assignments are done on single lines
* fails when assignment line breaks across syscomment chunks
* ARGUMENTS
* @.procname the name of the stored procedure
* @.varname the name of the variable the assignment of which is sought
(without the '@.')
*/
create procedure ShowAssignment(@.procname sysname, @.varname
nvarchar(16)) AS
BEGIN
DECLARE @.proctext nvarchar(4000), @.pos smallint
-- collect the text of the procedure
DECLARE proctext CURSOR FAST_FORWARD FOR SELECT syscomments.text
FROM syscomments INNER JOIN sysobjects on syscomments.id =
sysobjects.id WHERE sysobjects.name = @.procname and sysobjects.type =
'p' AND sysobjects.uid = user_id()
OPEN proctext
WHILE @.PROCTEXT IS NULL OR @.@.fetch_status = 0
BEGIn
SET @.pos = 0 -- reinitialize your cursor. Not like a database
cursor. A text cursor.
FETCH FROM proctext INTO @.proctext
-- now, there's no guarantee the proc text won't break in the
middle of an assignment. But, into each life...
WHILE 0 < PATINDEX('%SET [@.]' + @.varname + '[ =]%',
substring(@.proctext, @.pos + 1, len(@.proctext) - @.pos - 1))
BEGIN
SET @.pos = @.pos + PATINDEX('%SET [@.]' + @.varname + '[ =]%',
substring(@.proctext, @.pos + 1, len(@.proctext) - @.pos))
IF 0 < charindex(char(13), @.proctext, @.pos)
PRINT SUBSTRING(@.proctext, @.pos, charindex(char(13),
@.proctext, @.pos) - @.pos)
ELSE -- if we break here, show what we've got
PRINT '(partial)' + SUBSTRING(@.proctext, @.pos,
len(@.proctext) - @.pos)
END
END
CLOSE proctext
DEALLOCATE proctext
END
GO|||Hi
Unless type contains unique values you will get potentially random results
from the update.
You could try using
Update mynumbers SET a=b * (d+e), c=(d+e) where type = 1
depending on what you want to achieve.
John
"bradsalmon" wrote:

> Hi all,
> Scratching my head and wondering if this is even possible, so thought
> I'd ask the collective for some advice before I get started.
> I've inherited a stored procedure which contains lots (over 5000 lines)
> of calculations and I'd like to be able to work from it, a calculation
> and the constituent parts. For example, say the sproc contains -
> DECLARE @.a
> DECLARE @.b
> DECLARE @.c
> DECLARE @.d
> DECLARE @.e
> select @.b=b, @.d=d, @.e=e from mynumbers where type = 1
> select @.c = @.d+@.e
> select @.a = @.b * @.c
> Update mynumbers SET a=@.a, c=@.c where type = 1
>
> Then I'd like to be able to write something, so that I can simply ask
> how the sproc calculates a and I would get back -
> a = b * c
> c = d + e
> Does that make sense? Any ideas what would be the easiest way to do
> this?
> Thanks in advance,
> Brad
>|||Only a human with the time to read through the code can do what you are
asking.
You can write code that will identify every occurance of the variable in
question, but that will only get you half the answer.
If a = b + c
you still need to know what b and c equal.
So:
b=x/y
x=y*d
Now go look up x, y, and d
Now, look up the calculations that go into x, y, and d and find the source
for each of them.
Now, substitute the original values in your formula to list out all the
pieces of the formula, along with the procedural logic which determines
which calculations to use. Not to mention the variables used in
if/then/else statements which control whether or not these values even get
set.
Congratulations, you have just recreated the original code.
The best you can do is go through the code in a good text editor with find
functions and try to figure it out. Make sure you pay particular attention
to what is going on within any nested ifs, cursors, etc. Any automated
script for this will give you the wrong formula every time.
"bradsalmon" <bradsalmon@.yahoo.com> wrote in message
news:1147183833.980625.52860@.j73g2000cwa.googlegroups.com...
> Hi all,
> Scratching my head and wondering if this is even possible, so thought
> I'd ask the collective for some advice before I get started.
> I've inherited a stored procedure which contains lots (over 5000 lines)
> of calculations and I'd like to be able to work from it, a calculation
> and the constituent parts. For example, say the sproc contains -
> DECLARE @.a
> DECLARE @.b
> DECLARE @.c
> DECLARE @.d
> DECLARE @.e
> select @.b=b, @.d=d, @.e=e from mynumbers where type = 1
> select @.c = @.d+@.e
> select @.a = @.b * @.c
> Update mynumbers SET a=@.a, c=@.c where type = 1
>
> Then I'd like to be able to write something, so that I can simply ask
> how the sproc calculates a and I would get back -
> a = b * c
> c = d + e
> Does that make sense? Any ideas what would be the easiest way to do
> this?
> Thanks in advance,
> Brad
>