Show Line of Code Executing in Procedure

  • Chris Graham-202077

    Grasshopper

    Points: 17

    Comments posted to this topic are about the item Show Line of Code Executing in Procedure

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6359

    here is a small enhancement that will make it show more characters on a SQL Server 2005 box.

    USE vdc

    exec usp_DeleteIfExists 'dba_ShowCodeLine', 'STORED PROC'

    go

    CREATE PROCEDURE dbo.dba_ShowCodeLine

    (

    @SPID smallint,

    @WAIT tinyint = 0,

    @NoLoop bit = 0

    )

    AS

    BEGIN

    /*

    -- from http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1818

    Description: Show line of code executing - more details than dbcc inputbuffer

    Pre_condition:

    Post_condition:

    Input:

    Output:

    Change History: $Date: 18-07-07 8:38 $ , $Author: Hespo $, $Revision: 1 $

    Use like this:

    first you obtain a spid from SSMS -> Activity Monitor or EXEC sp_who2

    here I would like to see what spid=52 is doing:

    This sproc replaces

    dbcc inputbuffer (52) -- returns useles info

    with

    EXEC dba_ShowCodeLine 52 -- shows real info!

    As it will show in much higher detail, what the SQL Server is doing.

    2007-11-28 hespo - enhanced to show more than 2044 char on sql server 2005

    */

    SET NOCOUNT ON

    DECLARE @sql_handle binary(20), @handle_found bit

    DECLARE @stmt_start int, @stmt_end int

    DECLARE @line nvarchar(max), @wait_str varchar(8)

    SET @handle_found = 0

    IF @WAIT NOT BETWEEN 0 AND 60

    BEGIN

    RAISERROR('Valid values for @WAIT are from 0 to 60 seconds', 16, 1)

    RETURN -1

    END

    ELSE

    BEGIN

    SET @wait_str = '00:00:' + RIGHT('00' + CAST(@WAIT AS varchar(2)), 2)

    END

    WHILE 1 = 1

    BEGIN

    SELECT @sql_handle = sql_handle,

    @stmt_start = stmt_start/2,

    @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END

    FROM master.dbo.sysprocesses

    WHERE spid = @SPID

    AND ecid = 0

    IF @sql_handle = 0x0

    BEGIN

    IF @handle_found = 0

    BEGIN

    RAISERROR('Cannot find handle or the SPID is invalid', 16, 1)

    RETURN -1

    END

    ELSE

    BEGIN

    RAISERROR('Query/Stored procedure completed', 0, 1)

    RETURN 0

    END

    END

    ELSE

    BEGIN

    SET @handle_found = 1

    END

    SET @line =

    (

    SELECT

    SUBSTRING( text,

    COALESCE(NULLIF(@stmt_start, 0), 1),

    CASE @stmt_end

    WHEN -1

    THEN DATALENGTH(text)

    ELSE

    (@stmt_end - @stmt_start)

    END

    )

    FROM ::fn_get_sql(@sql_handle)

    )

    RAISERROR(@line, 0, 1) WITH NOWAIT

    IF LEN(@line) > 2048 BEGIN

    --PRINT '***********'

    SET @line = SUBSTRING(@line, 2045, 2048)

    RAISERROR(@line, 0, 1) WITH NOWAIT

    end

    IF @NoLoop = 1

    BEGIN

    RETURN 0

    END

    WAITFOR DELAY @wait_str

    END

    END

  • sahap

    Grasshopper

    Points: 17

    Everything goes well until I try to execute-

    FROM sys.dm_exec_sql_text(@sql_handle)

    This statement requires someone to have sysadmin permission which is not an option for developers. I intend to give this tool to developers so that they can investigate log running procedure.

    Is there a way to get around the permission? Or, Can the permission is only granted on the function to a group (developers) so that they can execute the show line of codes?

    Thanks,

    Prodip

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

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