June 30, 2008 at 9:11 pm
Is there a way to determine the name of the procedure that called a procedure? If a procedure is 5 levels deep in a bunch of nested procedure calls, is it possible to get the parent procedure?
The DBCC INPUTBUFFER command, from what I can tell, is showing the name of the top procedure that started the whole process.
And for whatever reason, when I try to use the table, I do not get the expected results. I try the following code and I get the CREATE PROCEDURE statement.
SELECT t.text as SQL_Statement, @@spid
FROM sys.dm_exec_requests As r
OUTER APPLY sys.dm_exec_sql_text(Sql_handle) As t
WHERE session_id = @@spid
I am trying to create a standard error handling procedure to log errors. I can get the procedure name from ERROR_PROCEDURE(), but I would like to know how that procedure was called. If I could get the parameters that would be ideal. The DBCC method gets the parameters if it is the top level, but these errors are most likely to appear 3-4 levels deep and I need the immediate parent procedure.
Am I chasing ghosts here? Can it be found?
Mike
July 1, 2008 at 6:32 am
Mike:
You might be able to use expression "OBJECT_NAME(@@ProcID)" to obtain the name of a calling procedure. This information could in turn be stored in the CONTEXT_INFO to save the name of the calling procedure. However, the size limit of the CONTEXT_INFO table is 128 binary bytes and this will not be sufficient to store any kind of list of object names.
Therefore, additional work is necessary. It might be better to store both (1) dbid and (2) @@procid in the context info rather than the name of the object. Also, you might be able to create a permanent table that you update as you go to contain this data. I don't like this latter idea because this type of thing can HELP you into a new set of deadlock and lock/block experiences. Give a look at:
+ Object_Name() function
+ @@ProcId
+ SET CONTEXT_INFO command
+ Context_Info() function
I hope this helps for a start
July 7, 2008 at 11:05 am
Thanks, Kent. I never heard of the Context stuff before, so that was very helpful. It still acts as a way of tracking things across procedures, without passing the info as parms. I am considering that a last option, so I don't have to add more logging and such, but so far it seems the only option.
July 7, 2008 at 11:10 am
An article that shows some use of CONTEXT_INFO can be found here at the MS Common Solutions site:
While this is not the same thing that you are wanting, it does show some examples of how to use the CONTEXT_INFO in a different setting.
Kent
July 8, 2008 at 7:00 pm
And for whatever reason, when I try to use the sys.dm_exec_requests table, I do not get the expected results. I try the following code and I get the CREATE PROCEDURE statement.
SELECT t.text as SQL_Statement, @@spid
FROM sys.dm_exec_requests As r
OUTER APPLY sys.dm_exec_sql_text(Sql_handle) As t
WHERE session_id = @@spid
Can someone explain why the sys.dm_exec_requests table shows the CREATE PROCEDURE statements instead of the latest SQL statement, even when executed 3 levels deep in a stored procedure?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply