September 23, 2007 at 6:17 pm
Comments posted to this topic are about the item Show Line of Code Executing in Procedure
November 26, 2007 at 12:25 pm
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-2 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-2 = 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-2 = 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
April 28, 2008 at 5:43 pm
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply