Getting the current code for a connection

[ijrussell]

Hi

I'm trying to see what code is running for a specific connection. I can do this using the Profiler - for a specific SPID and filtering for just the StmtCompleted events. (using SQL 2005)

However, I'd like to do this from Query Analyzer - and thought I could using:

SELECT @lcSQL = Text FROM sys.dm_exec_sql_text(@Handle)

Where @Handle is set based on the Spid.

This works in most circumstances, but where a stored procedure calls another stored procedure, all I seem to get is the code that defines the SP. i.e. The complete CREAT PROCEDURE xx AS code, code, code.

Is there anyway to do what I'm after? or do I just need to settle for the profiler?

Thanks

Ian