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 (AT) gmail (DOT) com wrote:
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 (AT) gmail (DOT) com> wrote