Show Line of Code Executing in Procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Chris Graham » Show Line of Code Executing in Procedure

Show Line of Code Executing in Procedure

Rate Topic

Display Mode

Topic Options

Author

Message

Chris Graham-202077

Posted Sunday, September 23, 2007 6:17 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 15, 2010 5:14 AM
Points: 1, Visits: 55

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

Post #401741

henrik staun poulsen

Posted Monday, November 26, 2007 12:25 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:20 AM
Points: 1,131, Visits: 854

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

Post #425952

sahap

Posted Monday, April 28, 2008 5:43 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 28, 2008 5:35 PM
Points: 1, Visits: 1

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

Post #491785

« Prev Topic | Next Topic »

Permissions