Using result of sp_lock in Stored Procedure

[Chuen Cain]

In my procedure, I need to use the results returned by
sp_lock for further processing. It is similar to a SELECT
on OPENQUERY(). For example,

Declare cursor CUR ...
....
For select * from (sp_lock)
Fetch next from CUR into
@spid, @dbid, ...

My intention is to loop through the results return by
sp_lock for further processing. I still can't a way to do
it.

Thanks for any advice!

[Dejan Sarka]

Prepare a temp table for the results, insert sp_lock rowset in the temp
table and declare the cursor on the temp table:
CREATE TABLE #Lockinfo(
spid smallint,
dbid smallint,
ObjId int,
IndId smallint,
Type nchar(4),
Resource nchar(16),
Mode nvarchar(8),
Status nvarchar(5))
GO
INSERT INTO #Lockinfo
EXEC sp_lock
SELECT * FROM #Lockinfo

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Chuen Cain" <seo...@magix.com.sg> wrote in message
news:1c0b01c2b6f0$a3127fa0$cef82ecf@TK2MSFTNGXA08...

>
> In my procedure, I need to use the results returned by
> sp_lock for further processing. It is similar to a SELECT
> on OPENQUERY(). For example,
>
> Declare cursor CUR ...
> ...

[Chuen Cain]

Thanks for the info! I just got it working too. Just
unfortunate that it can't be done in OPENQUERY() style.
Using temp table requires pre-knowledge of the table
structure instead of just knowing which columns I need.

Anyway, it still works. Thanks!

>.
>

[Dejan Sarka]

> Thanks for the info! I just got it working too. Just
> unfortunate that it can't be done in OPENQUERY() style.
> Using temp table requires pre-knowledge of the table
> structure instead of just knowing which columns I need.

You can do it using a workaround, which I would not recommend. You can
define local server as a linked one and then use openquery:

EXEC sp_addlinkedserver
@server='newname',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='servername'
GO

SELECT *
FROM OPENQUERY(newname,'EXEC sp_lock')