Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    229

    Unanswered: fn_get_sql , no full SQL data

    I'm struggling to get anything out of this fn_get_sql() thing as included below.
    First, running this in Q.A. still doesn't return the full SQL string, just the first x characters. Second, for most processes the sql_handle in Sysprocesses is just zeroes. Does this info get lost after a while? All the processes where I need to know what happened are old ones, that's waiting for NETWORKIO.


    drop proc dbo.getsql
    go
    create proc dbo.getsql as
    begin

    declare @sql_handle binary(8000)
    select @sql_handle = sql_handle from sysprocesses where spid = 112
    -- select spid,sql_handle from sysprocesses where spid = 79
    -- PRINT @SQL_HANDLE
    SELECT * from ::fn_get_sql(@sql_handle)

    end

    exec dbo.getsql

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    --------Try this sp deveolped by vyas--------
    Code:
    use master
    go
     
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    
    create  PROC dbo.ShowCodeLine
    (
     @SPID smallint,
     @WAIT tinyint = 0,
     @NoLoop bit = 0
    )
    AS
    BEGIN
     
     --Author: Narayana Vyas Kondreddi
     --Date Created: 20031016
     --Source: http://vyaskn.tripod.com
     
     
     SET NOCOUNT ON
     
     DECLARE @sql_handle binary(20), @handle_found bit
     DECLARE @stmt_start int, @stmt_end int
     DECLARE @line nvarchar(4000), @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
      
      /*
       --If you are running this on SQL Server 2005, then change the above line to the following:
       IF(@sql_handle = 0x0) OR (@stmt_start = 0 AND @stmt_end = 0)
       --Without this change, this procedure might go into an infinite loop and needs to be killed
       --In SQL Server 2000, as soon as a batch completed, 
       --the sql_handle column in sysprocesses becomes 0, but that's not the case in SQL Server 2005
      */
      
      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 @NoLoop = 1
      BEGIN
       RETURN 0
      END
     
      WAITFOR DELAY @wait_str
     
     END
     
    END
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    fn_get_sql only works for spids that are currently in a running/runnable state. You can try dbcc inputbuffer for anything not currently running.

    For the 255 character problem, have you adjusted your settings in Query Analyzer? GO to tools->options results tab, and increase the length there.

    How are you getting a waittype of NETWORK_IO? This is a very common LastWaittype value, but if a thing is currently waiting on a network read/write, you have to check the waittype value in sysprocesses. Network_IO is 0x800.

  4. #4
    Join Date
    Nov 2005
    Location
    Copenhagen, Denmark
    Posts
    5
    I've already tried the ShowCodeLine procedure, but I all get is its own error message about invalid handle. Probably because it's about a process that's not running/runnable anymore.

    I'm getting processes stuck in NETWORKIO for some reason. They are UPDATE:ing or INSERT:ing a record having an Image column among others. Since the binary value is so long, I can't see the WHERE clause because of the 255 limit. I'll look at that Q.A. setting tomorrow (it's evening here right now).

    I am checking the waittype in Sysprocesses, yes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •