I have been using fn_get_sql for sometime now to get information on
running processes. I have been passing the handle of an active SPID and
fn_get_sql returns the text of the stored procedure that is running.

Then I came accross a script that someone wrote that gets the actual
command that is running within the stored proc
(http://vyaskn.tripod.com/fn_get_sql.htm). But I don't understand what
the script is doing. The main jist of it is:

DECLARE @sql_handle binary(20), @handle_found bit
DECLARE @stmt_start int, @stmt_end int
DECLARE @line nvarchar(4000), @wait_str varchar(8)

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

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)

Can someone explain what dividing the stmt_start and stmt_end values by
2 does?

Thanks!

On 8 Jun 2006 15:17:35 -0700, pshro***@gmail.com wrote:

(snip)
Hi pshroads,

This converts the number of bytes taken by the double-byte Unicode data
to the number of characters that the SUBSTRING function expects.

Help! Users can't connect to database
Bring back Query Analyser
Default constraints in SQL Server 2000
HOW "TOP" is interpreted??
UNION problem