SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Show Line of Code Executing in Procedure


Show Line of Code Executing in Procedure

Author
Message
Chris Graham-202077
Chris Graham-202077
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 55
Comments posted to this topic are about the item Show Line of Code Executing in Procedure
henrik staun poulsen
henrik staun poulsen
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3280 Visits: 1245
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
sahap
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search