Last Running Query Based on SPID

Got this from Pinal Dave's Blog. Very useful Blog:

http://blog.sqlauthority.com

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = [SPID ID you wish to query]
SELECT TEXT, USER
FROM sys.dm_exec_sql_text(@sqltext);

In the future sys.sysprocesses is going to depreciated by Microsoft in favour of the System DMV's. 

Unfortunately unlike sys.sysprocesses, sys.dm_exec_requests only holds sql handles for active sessions. Microsoft have advised that deprecation of sys.sysprocesses will not happen until ALL functionality can be replaced by the DMV's, and clearly there is still some way to go....

Once the sys.dm_exec_requests DMV is fixed so it holds the handles for all sessions I would say that the above could be changed to:

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.dm_exec_requests
WHERE session_id = [SPID ID you wish to query]
SELECT TEXT, USER
FROM sys.dm_exec_sql_text(@sqltext);