Provide function to retrieve the entire call stack - by Erland Sommarskog

Status : 

 


139
0
Sign in
to vote
ID 124537 Comments
Status Active Workarounds
Type Suggestion Repros 10
Opened 12/21/2005 1:54:01 PM
Access Restriction Public

Description

We have several log tables where among other things save the name of the current stored procedure. Typically, there is a dedicated stored procedure that writes to a log table, and we pass @@procid to a parameter.

But there are cases where this is not really enough. Maybe the stored procedure that called the logging procedure itself is a general procedure. Or if we want log something by means of a trigger, we want to know the stored procedure that fired the trigger.

The purpose for this information, is mainly for tracking down problems in the application, and how some funny data may have ended up.

So what would be nedded would be a way to retrieve the entire call stack. At a minimum, we need database, schema and module (preferrably as names, rather than ids). Dynamic SQL would be represented by some placeholder.
Real nice would also be to have the line number where the next item on the stack was invoked, and for a real luxury actual parameter values would also be included.

Note that here is an important security observation: since the stored procedure are typically run by non-priv users, it must not be the case that such a function returns nothing, because the user does not have VIEW DEFINITION on the objects. This needs to be solved, for instance by relying on ownership chaining. Or from the simple fact, that if the module is on the call stack, the user indeeds has some permission to execute them.
Sign in to post a comment.
Posted by John Hardin on 5/4/2017 at 10:34 AM
Twelve years now, and nothing but crickets?

How many votes does something need before MSFT will *seriously* consider implementing it?
Posted by JRStern on 7/23/2016 at 12:15 AM
Yes, still a valid issue, needed it today, we have tons of existing code where we do it the hard way, passing in names from callers.
Posted by Eidolon on 4/5/2016 at 11:28 AM
Yes. Please. Pretty please. With a cherry on top?

We have an issue right now, where certain emails are going out from a sproc, but we cant find any reason why this sproc would be getting run.
It would be SOOOOO incredibly helpful, to be able to see the stack trace and see what the heck is calling the sproc.
Posted by SAinCA on 6/4/2013 at 12:14 PM
June 4th, 2013: "Voting is no longer enabled on this topic" - so is Microsoft about to give us this feature in SQL 2014 (2012 R2)?

+1 in lieu of the inactive voting button.
Posted by Anton Plotnikov on 6/4/2013 at 4:12 AM
Guys, this feature is unavailable for us more then ten years. Please add it to SQL Server 2012.
Posted by Shawn Eary on 2/23/2010 at 8:19 PM
In addition to a standard T-SQL callstack of functions/stored procedures, it would also be useful if the "call stack" could also "magically" indicate which if any *.NET methods triggered the stored procedure.

Suppose I have a VB.NET function like
Public Function GetDataSource() As DataView
' return result of T-SQL Stored Called sp_makeInventoryDataSource
End Function

Then suppose sp_makeInventoryDataSource called a T-SQL function called fn_getItemName(itemID).

Then the calls stack for fn_getItemName should not only contain sp_makeInventoryDataSource but it should also show the VB.NET function GetDataSource and might look like:
[T-SQL CAll] fn_getItemName
[T-SQL Call] sp_makeInventoryDataSource
[VB Call] GetDataSource
Posted by Rajib.Bahar on 10/15/2009 at 6:32 AM
I'd find it useful for debugging purpose. Also, debugging option in SQL 2008 meets my need to some degree.
Posted by John Couch on 8/5/2009 at 4:44 AM
I have to agree with Erland. A callstack would be awesome. Someway of logging it outto an eventlog or table. I have been trying to figure out how to create such a thing to show the where the error occured back up through to the originating call.
Posted by Anton Plotnikov on 6/26/2009 at 12:33 AM
It is a very useful feature, especially for debugging and tech supporting
Posted by aamirghanchi on 3/18/2009 at 10:03 AM
Not sure if you have added this feature to SQL 2008, but please do add it to next release of SQL 2005.
Thanks!

Aamir
Posted by Bjorn D. Jensen on 10/5/2008 at 3:28 AM
yes, it would be really helpfull if we could do things like
using dbms_stack or dbms_trace in Oracle PL/SQL.
/B. D. Jensen
Posted by Jamie Thomson on 4/7/2008 at 12:18 PM
This would be of use in the scenario I describe here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=189211

-Jamie
Posted by Erland Sommarskog on 10/19/2007 at 2:15 PM
Any update on this? I happened to see this in the event Blocked Process Report:

<executionStack>
    <frame line="17" stmtstart="1332" stmtend="2906" sqlhandle="0x03000d00399f00558c840601a29800000100000000000000"/>
    <frame line="1" sqlhandle="0x01000d00638e300f302e10ac000000000000000000000000"/>
</executionStack>

That is, here the call stack is available. Through the sqlhandle it is possible to retrieve
the objectids of the calling procedures.

If if it's available this way, it can't be too difficult to expose this in a DMV. Only exposing the
sqlhandle would not match with my observation with permissions above, but since you probably
only want this code in a few places, this could be dealt with adding extra privs with
certificate signing.
Posted by Microsoft on 10/16/2006 at 11:24 AM
Hi Erland,

Thanks for bringing this suggestion to our attention. This would definitly be a useful feature and we are considering implementing it in the next version of SQL Server.

Thanks,
Tomer Verona
SQL Engine Development