uncommented stored procedure help

[bcardona]

we are using sql server 2005 and im not familiar with stored uncommented procuedures, i was just handed and have no idea how to create or run it, any help is appreciated, i included the entire code.


DECLARE @PID

DECLARE @LOCKS TABLE
(
spid int,
dbid int,
ObjId int,
IndId int,
Type nvarchar(50),
Resource nvarchar(50),
Mode nvarchar(50),
Status nvarchar(50)
)

INSERT INTO @LOCKS (spid, dbid, ObjId, IndId, Type, Resource, Mode, Status) FROM sp_lock

DECLARE @IXMODE = SELECT * FROM @LOCKS WHERE Mode = 'IX'

EXECUTE sp_who2 144

select * from sys.dm_exec_sessions where session_id = 144







SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
AS
BEGIN
DECLARE @PID;

SET NOCOUNT ON;

DECLARE @LOCKS = EXECUTE sp_lock

DECLARE @IXMODE = SELECT * FROM @LOCKS WHERE Mode = 'IX'

EXECUTE sp_who2 @PID

select * from sys.dm_exec_sessions where session_id = 144
END
GO

 

[gmmastros]

Do you know what this code is supposed to do?

There are several problems that I can identify.

First...

[tt][blue]DECLARE @PID[/blue][/tt]

Whenever you declare something, you MUST provide a data type. In this case, I suspect the @PID should be an integer, so...

DECLARE @PID [!]Integer[/!]

Then, you are declare @LOCKS as a table variable, I don't immediately see anything wrong with it.

The next line appears as though you are trying to insert in to the table variable from a system stored procedure (sp_lock). Of course, the syntax is wrong. When inserting data in to a table variable from a stored procedure, you need to use the insert..exec syntax, like this...

INSERT INTO @LOCKS (spid, dbid, ObjId, IndId, Type, Resource, Mode, Status) [!]Exec[/!] sp_lock

The next line... well.... it's completely wrong.
[tt][blue]DECLARE @IXMODE = SELECT * FROM @LOCKS WHERE Mode = 'IX'[/blue][/tt]

If I had to guess, I would say that you are trying to create another table variable and populate it with data from your @Locks table variable, but ONLY where the Mode = 'IX'. Since table variables only exist for the duration of the session in which it is run, it would make more sense just to delete from the table variable itself.

Delete From @Locks Where Mode <> 'IX'

Putting it all together...

DECLARE @PID Integer

DECLARE @LOCKS TABLE
(
    spid    int,
    dbid    int,
    ObjId    int,
    IndId    int,
    Type    nvarchar(50),
    Resource    nvarchar(50),
    Mode    nvarchar(50),
    Status    nvarchar(50)
)

INSERT INTO @LOCKS (spid, dbid, ObjId, IndId, Type, Resource, Mode, Status) Exec sp_lock

Delete From @Locks Where Mode <> 'IX'

EXECUTE sp_who2 144

select * from sys.dm_exec_sessions where session_id = 144

With these changes, your code will at least execute. What this code is supposed to tell you is still a mystery to me.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

EXECUTE sp_who2 144

select * from sys.dm_exec_sessions where session_id = 144

sp_who2 is undocumented while sys.dm_exec_sessions is not!

those two queries will return a very similar resultset. sys.dm_exec_sessions returns more, you can also get DBCC USEROPTIONS out of sys.dm_exec_sessions

why do you waste your time with sp_locks? on a 2005 machine use sys.dm_tran_locks, that is why the dynamic management views exist....to get rid of all that sp_, xp_ and dbcc junk from sql 2000

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

Ahm,
one problem here. You can't use INSERT ... EXEC syntax with TABLE variable.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

Boris,

You can use Insert...Exec with table variables in [!]SQL2005[/!].

Your statement is true for SQL2000.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

The real question now is in SQL 2008 (November CTP) can you also use INSERT EXEC with Table-Valued Parameter?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bcardona]

thanks guys, what is trying to be achieved is to see what session 144 is, i guess this is locking up the db, anyway can i just copy all the code and save it as a procedure then execute it or do i have to break it up into 2 seperate. sorry but im new with this.

thanks

 

[SQLDenis]

you can use 2 dmvs for that

select * from sys.dm_tran_locks l 
join sys.dm_exec_sessions s on l.request_session_id = s.session_id
where l.request_mode <> 'IX'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

O, GOD!
Sorry George, I read SQL Server version WRONG!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bcardona]

Denis, what do you mean two dmvs, the code you put there, would replace what i had?

 

[SQLDenis]

sys.dm_tran_locks replaces sp_lock
sys.dm_exec_sessions replaces sp_who2

joining them together (I gave you the code) eliminates all that junk you had before

Did you run the code?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bcardona]

Denis,

no not yet, i create a new procedure, copy your code and the just execute it, is that correct, sorry for the basic question, but procedures are new to me.

 

[mrdenny]

The real question now is in SQL 2008 (November CTP) can you also use INSERT EXEC with Table-Valued Parameter?

You should be able to provided that both table parameters are created with the same user defined data type. Which is just funky, it's a table within a table. I haven't tried it yet, because I'm to lazy to fire up SQL 2008 at the moment. I'll play with it tomorrow when I'm working all night and report back.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bcardona]

i guess this should work fine, i havent tried it yes, but im using sql 2005. let ya know and thank you.