Help With a Stored Proc Name
-
A long time ago, I found a stored procedure that showed me the sql that was being run when one of my stored procedures "hung" up. This stored procedure showed me the current sql that was being run, ultimately leading me to the problematic SQL statement. For some dumb reason, I did not save the script. Does anybody remember the name of that stored procedure?
-
dbcc inputbuffer (spid) ??
-
Nope, this proc was undocumented until the last release of books online.
-
How Bout
sp_who2
-- Retrieve the sql of this connection
DECLARE @handle binary(20)
SELECT @handle = sql_handle
FROM master..sysprocesses
WHERE spid = 54 -- Your Spid Here
SELECT [text]
FROM ::fn_get_sql(@handle)
-
YES!!!, thank you very much!!!
-
Must be that... that's undocummented in my bols
. -
... and for the record, this isn't a stored proc
. -
LOL, I know, I am guessing it's a FUNCTION!!!
-
Easier to find once you know what you're looking for
. -
LOL, it sure is.
-
It is documented in BOL, as long as you have the version of BOL that was updated by SP3.
-
It's easier if you use DBCC INPUTBUFFER(spid) as stated in an earlier post in the thread.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
If you have a large batch of SQL, then using fn_get_sql, you can find out the actual statement in the batch that is being executed. With dbcc inputbuffer, you have no idea which statement in the batch is being executed.
-
dbcc inputbuffer only gives you the first 255 characters of the statement. fn_get_sql will give you all text with these exclusions: "Microsoft® SQL Server™ cannot cache some Transact-SQL statements, such as bulk copy statements and statements with string literals larger than 8 KB. Handles to those statements cannot be retrieved using the fn_get_sql function." (from BOL)
Michelle
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply