SP_WHO2

  • How can I run to see what's the bufferinput when running sp_who2 ?

    Any Ideas?

  • dbcc inputbuffer (), where is the spid from sp_who2. You could write a procedure to use a view of the sysprocesses table and wrap a cursor around each spid returned from the view...

  • If Object_Id('TempDB..#Who') is not Null Drop Table #Who

    If Object_Id('TempDB..#DBCC') is not Null Drop Table #DBCC

    Create Table #Who

    (

       SPID1 Int,Status VarChar(64),Login VarChar(64),HostName VarChar(64),

       BlkBy VarChar(64),DBName VarChar(64),Command VarChar(64),CPUTime Int,

       DiskIO Int,LastBatch VarChar(64),ProgramName VarChar(64),SPID2 Int,

       [InputBuffer] nVarChar(256)

    )

    Create Table #DBCC (EventType VarChar(64),Parameters Int,EventInfo nVarChar(256))

    Insert dbo.#Who

    (

       SPID1,Status,Login,HostName,BlkBy,DBName,Command,

       CPUTime,DiskIO,LastBatch,ProgramName,SPID2

    )

    Exec sp_who2

    Declare @SPId Int,@SQL VarChar(256),@IB nVarChar(256)

    Declare Csr Cursor for Select SPId1 from dbo.#Who Open Csr

    Fetch Next from Csr into @SPId

    While @@Fetch_Status=0 Begin

       Truncate Table dbo.#DBCC

       Set @SQL='DBCC InputBuffer('+Cast(@SPId as VarChar)+')'

       Insert dbo.#DBCC Exec(@SQL)

       Set @ib-2=Case @@RowCount when 1 then (Select IsNull(EventInfo,'') from #DBCC) else N'' End

       Update dbo.#Tmp set [InputBuffer]=@IB where SPID1 = @SPId

       Fetch Next from Csr into @SPId

    End

    Close Csr Deallocate Csr

    Select * from dbo.#Tmp

     



    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

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

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