• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1159
  • Last Modified:

How to get an ObjId if you have an SPID - Stored Procedure

SQL Server 2000
I am working with detecting (in the future) an object name in a proc.

I'm working to have a proc that I can run and find the table that is locked.  I can run:

execute sp_Lock @vcSPID  
         where @vcSPID = the SPID from the locked process

Inside the table created by sp_Lock (in my case 13 records) I have an objId that equals = 0 on record 1 and the objId of the locked object in the field ObjId on the other records.  

I want to put that ObjId in a variable that I can use later SELECT object_name(@intObjId) .

Thanks in advance for any help.
I want to put
1 Solution
Lara FEACommented:
You can get result  from execute into table, then read data from it

insert into #tbl
execute sp_Lock @vcSPID  

table needs to be created to match result set returned from sp
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now