Post reply

sp_who AND Name of Stored Procedure

  • March 24, 2010 at 9:17 am

    #139947

    Hello all,

    is there a chance to get a correlation between the SPID from sp_who and the Stored Procedures Name?

    Greetz

    Stefan

  • March 24, 2010 at 9:39 am

    #1139012

    it sounds like you want to know the last command a spid happened to issue...in your case you think it was a stored procedure:

    SELECT SDEC.[most_recent_session_id],DEST.TEXT

    FROM sys.[dm_exec_connections] SDEC

    CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST

    --WHERE SDEC.[most_recent_session_id] = @spid

    sample results:

    most_recent_session_id TEXT

    51 EXEC sp_trace_generateevent 86, N'ApexSQL Connection Monitor Heartbeat event'

    52 create procedure sys.sp_trace_getdata (@traceid int, @records int = 0 ) as select * from OpenRowset(TrcData, @traceid, @records)

    53 SELECT SDEC.[most_recent_session_id],DEST.TEXT FROM sys.[dm_exec_connections] SDEC CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST --WHERE SDEC.[most_recent_session_id] = @spid

    54 (@_msparam_0 nvarchar(4000))SELECT CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible] FROM master.sys.databases AS dtb WHERE (dtb.name=@_msparam_0)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 25, 2010 at 2:12 am

    #1139445

    Hey Lowell,

    thank your for your reply. It seems to work. Indeed it shows me "CREATE STORED PROCEDURE" when I'm running the Procedure via EXEC, but it's ok to identify the nasty procedure...

    greetz

    Stefan

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply