Can't get fn_get_sql to work

I can't seem to get the fn_get_sql function to work. Here's the script
I'm using:
declare @.spid int
DECLARE @.Handle binary(20)
declare active_spids cursor for select spid, sql_handle from
master..sysprocesses where status not in ('background', 'sleeping')
--and spid <> @.@.spid
open active_spids
fetch next from active_spids into @.spid, @.handle
while @.@.fetch_status = 0
begin
select @.spid as spid,@.handle as sql_handle
SELECT @.Handle = sql_handle FROM master..sysprocesses WHERE spid =
@.spid
SELECT * FROM ::fn_get_sql(@.Handle)
fetch next from active_spids into @.spid, @.handle
end
close active_spids
deallocate active_spids
I never get any result set back from the function:
spid sql_handle
-- ---
166 0x01000600D8753C019840E7930000000000000000
(1 row(s) affected)
dbid objectid number encrypted text


-- -- -- -- --
(0 row(s) affected)
spid sql_handle
-- ---
280 0x0100060099BF3103984033BA0000000000000000
(1 row(s) affected)
dbid objectid number encrypted text


-- -- -- -- --
(0 row(s) affected)
Any help would be appreciated!
I just tried it and verified your code.
It does not work for me either.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
It was introduced in 8.00.652, and was publicly available in SP3. Are you
also using SP4?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<pshroads@.gmail.com> wrote in message
news:1118444562.461080.161880@.g47g2000cwa.googlegr oups.com...
>I can't seem to get the fn_get_sql function to work. Here's the script
> I'm using:
> declare @.spid int
> DECLARE @.Handle binary(20)
> declare active_spids cursor for select spid, sql_handle from
> master..sysprocesses where status not in ('background', 'sleeping')
> --and spid <> @.@.spid
> open active_spids
> fetch next from active_spids into @.spid, @.handle
> while @.@.fetch_status = 0
> begin
> select @.spid as spid,@.handle as sql_handle
> SELECT @.Handle = sql_handle FROM master..sysprocesses WHERE spid =
> @.spid
> SELECT * FROM ::fn_get_sql(@.Handle)
> fetch next from active_spids into @.spid, @.handle
> end
> close active_spids
> deallocate active_spids
>
>
> I never get any result set back from the function:
>
> spid sql_handle
> -- ---
> 166 0x01000600D8753C019840E7930000000000000000
> (1 row(s) affected)
> dbid objectid number encrypted text
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> -- -- -- -- --
> (0 row(s) affected)
> spid sql_handle
> -- ---
> 280 0x0100060099BF3103984033BA0000000000000000
> (1 row(s) affected)
> dbid objectid number encrypted text
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> -- -- -- -- --
> (0 row(s) affected)
>
> Any help would be appreciated!
>
|||pshroads@.gmail.com wrote:
> I can't seem to get the fn_get_sql function to work. Here's the script
> I'm using:
> declare @.spid int
> DECLARE @.Handle binary(20)
> declare active_spids cursor for select spid, sql_handle from
> master..sysprocesses where status not in ('background', 'sleeping')
> --and spid <> @.@.spid
> open active_spids
> fetch next from active_spids into @.spid, @.handle
> while @.@.fetch_status = 0
> begin
> select @.spid as spid,@.handle as sql_handle
> SELECT @.Handle = sql_handle FROM master..sysprocesses WHERE spid =
> @.spid
> SELECT * FROM ::fn_get_sql(@.Handle)
> fetch next from active_spids into @.spid, @.handle
> end
> close active_spids
> deallocate active_spids
>
>
> I never get any result set back from the function:
>
> spid sql_handle
> -- ---
> 166 0x01000600D8753C019840E7930000000000000000
> (1 row(s) affected)
> dbid objectid number encrypted text
>
> -- -- -- -- --
> (0 row(s) affected)
> spid sql_handle
> -- ---
> 280 0x0100060099BF3103984033BA0000000000000000
> (1 row(s) affected)
> dbid objectid number encrypted text
>

> -- -- -- -- --
> (0 row(s) affected)
>
> Any help would be appreciated!
Works for me with SP3a:
dbid objectid number encrypted text
NULL NULL NULL 0 declare @.spid int
DECLARE @.Handle binary(20)
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||It works on SP4.
Try enabling trace flag 2861. It will force SQL to keep zero-cost plans in
the cache. It may also grow your procedure cache and cause a whole lot of
other performance problems. See Q325607 for details.
If you are looking for ways to audit activity on your SQL Server you may
want to consider using dbcc inputbuffer instead of fn_get_sql.
Adrian
<pshroads@.gmail.com> wrote in message
news:1118444562.461080.161880@.g47g2000cwa.googlegr oups.com...
>I can't seem to get the fn_get_sql function to work. Here's the script
> I'm using:
> declare @.spid int
> DECLARE @.Handle binary(20)
> declare active_spids cursor for select spid, sql_handle from
> master..sysprocesses where status not in ('background', 'sleeping')
> --and spid <> @.@.spid
> open active_spids
> fetch next from active_spids into @.spid, @.handle
> while @.@.fetch_status = 0
> begin
> select @.spid as spid,@.handle as sql_handle
> SELECT @.Handle = sql_handle FROM master..sysprocesses WHERE spid =
> @.spid
> SELECT * FROM ::fn_get_sql(@.Handle)
> fetch next from active_spids into @.spid, @.handle
> end
> close active_spids
> deallocate active_spids
>
>
> I never get any result set back from the function:
>
> spid sql_handle
> -- ---
> 166 0x01000600D8753C019840E7930000000000000000
> (1 row(s) affected)
> dbid objectid number encrypted text
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> -- -- -- -- --
> (0 row(s) affected)
> spid sql_handle
> -- ---
> 280 0x0100060099BF3103984033BA0000000000000000
> (1 row(s) affected)
> dbid objectid number encrypted text
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> -- -- -- -- --
> (0 row(s) affected)
>
> Any help would be appreciated!
>