Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006

    Unanswered: joining sysprocesses with fn_get_sql

    ok, I have a requirement where I need to get a list of sql commands currently being blocked.
    This is very easy to do via stored procedure, and I have it working well using a console app to fire it off.

    Trouble is, I need to ship it to different offices on an ad hoc basis. I don't want to install a stored procedure on each site because it'll be a one off job and there is not likely to be anyone available who would know how to even install a new sp. So, I thought I'd try and pull back the sql commands via a select statement, joining together sysprocesses and the fn_get_sql udf. This returns a table, so I presumed I could join the two together using a subquery via the sql_handle with something like this :

    SELECT sql_handle,
    SELECT top 1 [text] FROM ::fn_get_sql(sysprocesses.sql_handle)
    ) as sqlcommand

    FROM master..sysprocesses

    The error back is incorrect syntax near 'sysprocesses'. I can't see if I'm doing anything obvious wrong.

    Anyone any ideas? I'm using SQL Server 2000 sp3.

  2. #2
    Join Date
    Aug 2002
    A BOL example for using fn_get_sql:
    DECLARE @Handle binary(20)SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52SELECT * FROM ::fn_get_sql(@Handle) HTH
    --Satya SKJ
    Microsoft SQL Server MVP

  3. #3
    Join Date
    May 2006
    thanks for your reply, however, that will only work in a stored procedure.
    I won't be able to install a sp, as it needs to be completely standalone.
    I'm trying to find a one-hit SQL query to do the job. It should work, but it doesn't.


  4. #4
    Join Date
    Dec 2009
    Did you ever find an answer?
    That's what I'd like to know how to do too

  5. #5
    Join Date
    Jan 2010

    SQL 2005 version

    select * from master..sysprocesses sp cross apply fn_get_sql(sql_handle)

  6. #6
    Join Date
    Dec 2009
    select * from master..sysprocesses sp cross apply fn_get_sql(sql_handle)
    I need one that works in SQL Server 2000 please, the above gives this error in SQL Server 2000:
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'apply'.

  7. #7
    Join Date
    Sep 2010

    SQLServer 2000 - join with fn_get_sql

    declare @sql_handle binary(20);
    declare c1 cursor for select sql_handle from sysprocesses where status='runnable';
    open c1;
    fetch next from c1 into @sql_handle;
    while (@@FETCH_STATUS =0)
    ,substring(b.program_name,1,20) as Program
    from ::fn_get_sql(@sql_handle) a
    , sysprocesses b
    , sys.sysdatabases c
    where b.sql_handle =@sql_handle
    and c.dbid=b.dbid
    fetch next from c1 into @sql_handle
    ;END close c1
    ;deallocate c1;

Posting Permissions

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