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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy