fn_get_sql Help

Question

Post reply

fn_get_sql Help

Glenn Owens

SSCrazy

Points: 2099
More actions
June 29, 2006 at 7:42 am

#168996

I must be missing something...
I have the following script that I'm hoping to use in order to get the full SQL 2000 SP4 statements (not just the first 255 chars):
declare @spid int
declare @handle binary(20)
set @spid = 1 -- set the target spid (must be currently active)
select @handle = sql_handle from master.dbo.sysprocesses where spid = @spid
dbcc inputbuffer(@spid)
select [text] from ::fn_get_sql(@handle)
Unfortunately, when this script is run it always returns a NULL value.
I'm in a situation right now that really would benefit from seeing the SQL statement!!! Is there something else that needs to be turned on???
Thanks!
Glenn

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi Glenn,

In your script you're checking for spid 1, which is background spid. All spids lower than 50 are background spids.

To get it to work try looking for an active spid higher than 50.

You can check on what spids are active by running sp_who2 active.

Hope that helps,

Karl
source control for SQL Server

Glenn Owens SSCrazy Points: 2099 More actions · Answer 2

Karl -

I realize that it appears that I'km checking spid 1 (it's just for an example). when the script is executed I change the spid to whatever one I'm interested in...

Glenn

Rick Lowrey Right there with Babe Points: 785 More actions · Answer 3

Try this:

declare @bigEventinfo nvarchar(4000)

declare @eventinfo nvarchar(257)

create table #x (

EventType nvarchar(30)

,Parameters Int

,EventInfo nvarchar(255)

)

insert #x exec('dbcc inputbuffer(1)') -- Change the spid, of course

select @eventinfo = left(eventinfo, len(eventinfo) - 2) + '%' from #x

select distinct @bigEventInfo = sql from master.dbo.syscacheobjects

where sql like @eventinfo

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 4

There is something definately wrong with fn_get_sql as documented in BOL. It looks like the sql_handle is only available to the @@SPID connection, RE:

SELECT spid, sql_handle FROM sysprocesses WHERE spid = @@SPID

vs

SELECT spid, sql_handle FROM sysprocesses WHERE spid = 88

So if @@SPID = 88 then both of the above return a sql_handle > 0x0, otherwise sql_handle will always be 0x0.

Even then using:

DECLARE @handle binary(20)

SELECT @handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = @@SPID

SELECT * FROM ::fn_get_sql(@handle)

Always returns an empty row, so maybe the BOL entry: "If you pass a handle that no longer exists in cache, fn_get_sql returns an empty result set." is not refering to the sysprocesses table as the "cache" but something else?

Andy