Last TSQL command - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Last TSQL command

Author

Topic

jshepler
Yak Posting Veteran

60 Posts

Posted - 2005-12-31 : 11:45:45

I'm making a database activitity page (don't want to make anyone sysadmin that don't need to be) and part of it shows root blocks. I would like to show what the SPID is running. I know I can get this with DBCC INPUTBUFFER(@SPID).

The problem is trying to use that with a set of SPIDs. I can't figure out how to return a set of SPIDs that includes the EventInfo column from the output of INPUTBUFFER.

This is basically what I want:


SELECT    SPID, Program = program_name, LastCommand = ????
FROM      sysprocesses
WHERE     blocked = 0
          AND SPID IN (SELECT DISTINCT blocked FROM sysprocesses)

I can't figure out what to do for LastCommand. I would prefer not to create a proc/function (we have many sql servers and it would be one more object that would need to be documented for maintenance, new builds, etc. as well as putting custom procs/funcs in master seems.... wrong... is it??). If I have to, I have to.

I did try to make a function, but failed miserably. Functions can't use temp tables and DBCC/EXEC can't be a source for table variables.

I can't figure out how to return the info from DBCC INPUTBUFFER in a way that can be used in a query. I really don't want to use a cursor either.

Any ideas?

/jeff

jshepler
Yak Posting Veteran

60 Posts

Posted - 2006-01-03 : 12:09:54

bump

/jeff

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-03 : 13:27:14

Jeff,
I didn't respond to this because as far as I know, there is no system table or view available to us that contains the <dbcc inputbuffer> info. So I know of no way to get a set-based, tabular output of lastCommand info for a multiple spids.

Paul Randal is the DBCC guy, maybe he'll pipe in to set us straight.

Be One with the Optimizer
TG

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-01-03 : 14:07:05

Doesn't Enterprise Manager show the last command when you go to /management/current activity/process info?

If EM can do it, there is a T-SQL command for it. The question of course is, what is that command. :)

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-01-03 : 15:48:22

>> If EM can do it, there is a T-SQL command for it. The question of course is, what is that command. :)

dbcc inputbuffer

fn_get_sql:
http://www.databasejournal.com/features/mssql/article.php/10894_2189761_1

rockmoose

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-03 : 16:05:39

>>fn_get_sql:
That's cool!

But he'll still have the same problem because it only works on one handle at a time, just like dbcc inputbuffer only works with one spid at a time.

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-01-03 : 16:52:01

>> But he'll still have the same problem because it only works on one handle at a time, just like dbcc inputbuffer only works with one spid at a time.

Yes, no set-based solution to this.

Jeff, did you look at nigel's sp_nrInfo proc? (http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html)
that should a very good start.

rockmoose

jshepler
Yak Posting Veteran

60 Posts

Posted - 2006-01-03 : 16:54:10

There we go!

Yes, it only works with one spid at a time, but I can wrap it in a function that I can use in a query:

[CODE]
CREATE FUNCTION fn_GetProcessInfo(@SPID int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @Handle binary(20), @retVal varchar(8000)

SELECT @Handle = sql_handle
FROM master..sysprocesses
WHERE SPID = @SPID

SELECT @retVal = convert(varchar(8000), [text])
FROM ::fn_get_sql(@Handle)

RETURN @retVal
END
[/CODE]

and use it like:
[CODE]
SELECT SPID, Program = program_name, LastCommand = dbo.fn_GetProcessInfo(SPID)
FROM sysprocesses
WHERE blocked = 0
AND SPID IN (SELECT DISTINCT blocked FROM sysprocesses)
[/CODE]

I'll have to run a couple of tests, but I think this will work just fine.

Thanks rockmoose!! :)

/jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources