Solved

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

Posted on 2010-08-26
1
1,124 Views
Last Modified: 2012-05-10
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.
LJG
I want to put
0
Comment
Question by:LJG
1 Comment
 
LVL 11

Accepted Solution

by:
Larissa T earned 500 total points
ID: 33535829
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
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now