Exact SQL for running process

For a long running stored procedure, how can I determine the SQL statement within the stored procedure that is currently running?

The Activity Monitor only shows the name of the stored procedure and whether it is a SELECT/INSERT/UPDATE, never the complete statement.

Thank you.

You could capture the "SP: StmtCompleted" event in Profiler.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Yes, I realize that is an option but would like to know if there is a way to do this with just T-SQL (using some system tables). I do not often have the Profiler running and it would be very useful to have a simpler way to get this information.

Thank you.

|||

You can get this through the system function sys.dm_exec_sql_text

You pass in the handle from sys.dm_exec_requests. Check books online under sys.dm_exec_requests and it will walk you through an example.

-Sue

|||

This still gives me the entire stored procedure text (similar to fn_get_sql). If I have a stored procedure containing the following statements:

SELECT * FROM Large_Table_1 (NOLOCK)

SELECT * FROM Large_Table_2 (NOLOCK)

SELECT * FROM Large_Table_3 (NOLOCK)

I would like to see the currently running select during execution. A T-SQL version of what the Profiler shows is really what I need.

Thank you.

|||

There is a begin and end offset of the current statement in those dmv's.

You should use substring to parse the text with the given offsets.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com