How to use fn_get_sql in SP3

[Unknown]

Hi:

We are trying to get the sql statement within a stored procedure. In SP3 for SQL2000, there is a new function fn_get_sql. However when we run that procedure for a particular spid, we do not get any text returned. The problem may be related to the sql_handle column being all 0s. However the DBCC INPUTBUFFER command does show the stored procedure. Do we have to anything else to make the fn_get_sql function to work. Any help would be appreciated.

Thanks,

-Sunil

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

[Jasper Smith]

DBCC INPUTBUFFER reads the input network buffer
(or something like that) so until a new batch is submitted,
the statement is available. However it seems that fn_get_sql
only has access to running sql. Open Query Analyzer and
open 2 query panes. In one run this

use Northwind
go
alter PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
waitfor delay '00:00:05'
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID
= P.ProductID
GROUP BY ProductName
go
exec dbo.CustOrderHist 'ALFKI'

and in the other run this (where @spid is the spid of the query above)

DECLARE @Handle binary(20)
DECLARE @spid int ; SET @spid = 51
SELECT @Handle = sql_handle FROM master..sysprocesses WHERE spid = @spid
SELECT * FROM ::fn_get_sql(@Handle)

You shoud see the text of CustOrderHist in the results from fn_get_sql()
because we have introduced an artificial delay so that it's still running
when we run fn_get_sql() in query 2.

--
HTH

Jasper Smith (SQL Server MVP)

I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Sunil G. Shenoy" <sunil....@quest.com> wrote in message
news:eZCj5U8yCHA.2424@TK2MSFTNGP10...