SQL Server 2000 Service Pack 3 introduced this new, supposedly useful, function, fn_get_sql. This is like dbcc inputbuffer() but is meant to have advantages, specifically:-

- it can return more than 256 characters of a spid's inputbuffer
- it can return more than just the execution of a stored procedure, it can give you the SQL an SP is executing, or if an SP calls another SP, it can give you the called SP rather than just the calling SP.

Well, that's all fine 'n' dandy, but can you get it to work??? I can't. Whenever I try to use it, I get no results.

I am using the following syntax (copied from BOL):-

DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52 --spid obviously changed to be an active one!
SELECT * FROM ::fn_get_sql(@Handle)


Am I doing anything wrong? Anyone else had any joy using it?

I can get info out of dbcc inputbuffer(), but this new function is not working "as it says on the tin", so to speak.

Tom Pullen
DBA, Oxfam GB

It don't work on my setup either. I'm getting a valid @Handle result back but the function doesn't return anything.

There's this snippet from BOL

If you pass a handle that no longer exists in cache, fn_get_sql returns an empty result set. If you pass a handle that is not valid, the batch stops, and the following error message is returned:

Maybe the key is "exists in cache"?

Yeah, I don't get it... exists in cache? If it's an active process, which I know it is, I'd assume that its handle MUST exist in cache... If you can run dbcc inputbuffer() on it, it works...

I do get a valid handle - I certainly don't get an error, just an empty results set.

So if it doesn't exist in cache when I run fn_get_sql, does it EVER exist in cache? Strange...



Tom Pullen
DBA, Oxfam GB

I experimented a bit and ran a transaction with waitfor in one QA window and fn_get_sql/DBCC INPUTBUFFER in the other, so that the transactionw as most definitly active when I ran fn_get_sql/DBCC INPUTBUFFER. Zilch nope nada for fn_get_sql.

Then I did a search and came across this post http://tinyurl.com/92er) by Jasper Smith who says that fn_get_sql will only give you sql that's actually executing. So it's only useful for really long running queries.

Thanks for that, Jacco. I am trying fn_get_sql with a long running query. But it won't work.

I tried with that Northwind example in the link you posted, where you alter an SP and insert a WAITFOR, which did work.

But why won't it work for a legitimate, long running query (execution of an SP)? I still don't know.

Tom Pullen
DBA, Oxfam GB

I realize that this was more than likely already resolved... but since there is no resolution to this thread I thought I would add one.

See the following:
http://www.databasejournal.com/features/mssql/article.php/10894_2189761_2

In particular, read the last part of the page (I pasted it below just in case the link does not work in the future):

Handles expire very quickly and should be used immediately. If you pass in the handle that is no longer in the cache, fn_get_sql returns an empty result set. That was happening to me often and I couldn't explain the empty results. It turns out that when the plan for a SQL Statement has zero cost it isn't cached at all.

Microsoft has added a remedy to this situation in the form of a new trace flag, 2861. Once it is turned on, zero cost plans are cached and they show up as the result of fn_get_sql. Trace flags are turned on with the DBCC TRACEON statement such as:
DBCC TRACEON (2861)

You can turn it back off with TRACEOFF as in:
DBCC TRACEOFF (2861)

Thanks very much for that, it's handy additional information.

Tom Pullen
DBA, Oxfam GB

You need SP3a for this to work properly.
Cheers,

I think your problem is that the job may open multiple threads. Some of these threads may have a handle value of zero and return a null response. Try making that the thread has a valid handle.

Example:

DBCC TRACEON (2861)
DECLARE @SPID int
DECLARE @kpid int
DECLARE @Handle binary(20)
SET @SPID = 131 --put your number here

SET @kpid = (Select kpid
from master..sysprocesses
where spid = @SPID
and sql_handle <> 0x0000000000000000000000000000000000000000 )
IF @kpid IS NOT NULL BEGIN
SELECT @Handle = sql_handle
FROM master..sysprocesses
WHERE kpid = @kpid
SELECT [text]
FROM ::fn_get_sql(@handle)
END
ELSE BEGIN
PRINT 'kpid is null'
END
DBCC TRACEOFF (2861)