Another sp_who3

  • Comments posted to this topic are about the item Another sp_who3

  • I get the following error when i execute this script. Thanks for the script.

    Msg 102, Level 15, State 1, Procedure sp_who3, Line 3

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_who3, Line 31

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_who3, Line 51

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_who3, Line 52

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_who3, Line 54

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_who3, Line 55

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Procedure sp_who3, Line 61

    Must declare the scalar variable "@s_spid".

    Msg 102, Level 15, State 1, Procedure sp_who3, Line 66

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_who3, Line 68

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_who3, Line 69

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Procedure sp_who3, Line 79

    Incorrect syntax near '?'.

  • Incorrect syntax near '?'.

    Would it be possible to get a revised script posted with the errors above?

    ...thanks

  • I get the same errors - not sure why - anyone know?

  • I get the same errors

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 30

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 50

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 51

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 53

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 54

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 60

    Must declare the scalar variable "@s_spid".

    Msg 102, Level 15, State 1, Line 65

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 67

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 68

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 78

    Incorrect syntax near '?'.

  • The indentation uses some weird characters. In my case I pasted it into an advanced text editor and they came up with question marks. I just replaced the question marks with spaces and it worked great.

    Also, if you want to run this in MSSQL 2000, replace the table variable with a temporary table (ie. @dbcctab becomes #dbcctab)

  • Roman Vinnik (4/27/2010)


    The indentation uses some weird characters. In my case I pasted it into an advanced text editor and they came up with question marks. I just replaced the question marks with spaces and it worked great.

    Also, if you want to run this in MSSQL 2000, replace the table variable with a temporary table (ie. @dbcctab becomes #dbcctab)

    Thanks for getting this cleared up... my first post using the required sql server central script plugin and it for some reason changed it to some wierd characters.

    Anyone know why this is happening as I was only copy-pasting from SSMS?

  • Here is a revised script...

    CREATE PROCEDURE [dbo].[sp_who3]

    @SPID INT = NULL,

    @DBName VARCHAR(255) = NULL,

    @running BIT = NULL,

    @blocked BIT = NULL,

    @eventinfo VARCHAR(100) = NULL

    AS

    SET NOCOUNT ON

    DECLARE @iSPID int

    CREATE TABLE #spwho (

    SPID int NOT NULL

    , Status varchar (255) NOT NULL

    , Login varchar (255) NOT NULL

    , HostName varchar (255) NOT NULL

    , BlkBy varchar(10) NOT NULL

    , DBName varchar (255) null

    , Command varchar (255) NOT NULL

    , CPUTime int NOT NULL

    , DiskIO int NOT NULL

    , LastBatch varchar (255) NOT NULL

    , ProgramName varchar (255) null

    , SPID2 int NOT NULL

    , REQUESTID int NOT NULL

    )

    CREATE TABLE #dbcc (

    SPID int,

    EventType varchar(255),

    Paramters int,

    EventInfo varchar(8000)

    )

    INSERT #spwho

    EXEC sp_who2

    DECLARE buf CURSOR FAST_FORWARD FOR

    SELECT SPID FROM #spwho

    OPEN buf

    FETCH NEXT FROM buf

    INTO @iSPID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    DECLARE @s_spid VARCHAR(10)

    SET @s_spid = CAST(@iSPID AS varchar(10))

    DECLARE @dbcctab TABLE (

    EventType varchar(255),

    Paramters int,

    EventInfo varchar(8000)

    )

    INSERT @dbcctab

    EXEC ('dbcc inputbuffer(' + @s_spid + ') WITH NO_INFOMSGS')

    INSERT #dbcc

    SELECT @iSPID, * FROM @dbcctab

    DELETE FROM @dbcctab

    FETCH NEXT FROM buf

    INTO @iSPID

    END

    CLOSE buf

    DEALLOCATE buf

    SET NOCOUNT OFF

    SELECT

    s.SPID,

    d.EventInfo,

    s.Status,

    s.Login,

    s.HostName,

    s.BlkBy,

    s.DBName,

    s.Command,

    s.CPUTime,

    s.DiskIO,

    s.LastBatch,

    s.ProgramName,

    s.REQUESTID

    FROM

    #spwho s

    LEFT JOIN #dbcc d ON

    s.SPID = d.SPID

    WHERE

    (@SPID IS NULL OR s.SPID = @SPID)

    AND (@blocked IS NULL OR (@blocked = 1 AND LTRIM(RTRIM(s.BlkBy)) != '.') OR (@blocked = 0 AND LTRIM(RTRIM(s.BlkBy)) = '.'))

    AND (@running IS NULL OR (@running = 1 AND s.Status != 'sleeping') OR (@running = 0 AND s.Status = 'sleeping'))

    AND (@DBName IS NULL OR s.DBName = @DBName)

    AND (@eventinfo IS NULL OR d.EventInfo LIKE @eventinfo)

    ORDER BY

    LastBatch DESC

  • Good work mate.

    Works good for me.

    Thanks

  • Kristian Ask (4/28/2010)


    Roman Vinnik (4/27/2010)


    The indentation uses some weird characters. In my case I pasted it into an advanced text editor and they came up with question marks. I just replaced the question marks with spaces and it worked great.

    Also, if you want to run this in MSSQL 2000, replace the table variable with a temporary table (ie. @dbcctab becomes #dbcctab)

    Thanks for getting this cleared up... my first post using the required sql server central script plugin and it for some reason changed it to some wierd characters.

    Anyone know why this is happening as I was only copy-pasting from SSMS?

    That problem happens frequently.

    A simple solution is to copy to notepad++ and then to SSMS after copying from SSC.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I find this to be an interesting solution.

    Nice Job.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 11 posts - 1 through 10 (of 10 total)

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