Provide function to retrieve the entire call stack
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.
Upvotes: 146<=-=Oct 16 2006 11:24AM=-=>
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.
SQL Engine Development
Any update on this? I happened to see this in the event Blocked Process Report:
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
This would be of use in the scenario I describe here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=189211
-Jamie<=-=Oct 5 2008 3:28AM=-=>
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
Not sure if you have added this feature to SQL 2008, but please do add it to next release of SQL 2005.
Aamir<=-=Jun 26 2009 12:33AM=-=>
It is a very useful feature, especially for debugging and tech supporting<=-=Aug 5 2009 4:44AM=-=>
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.<=-=Oct 15 2009 6:32AM=-=>
I’d find it useful for debugging purpose. Also, debugging option in SQL 2008 meets my need to some degree.<=-=Feb 23 2010 8:19PM=-=>
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
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
Guys, this feature is unavailable for us more then ten years. Please add it to SQL Server 2012.<=-=Jun 4 2013 12:14PM=-=>
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.<=-=Apr 5 2016 11:28AM=-=>
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.
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.<=-=May 4 2017 10:34AM=-=>
Twelve years now, and nothing but crickets?
How many votes does something need before MSFT will seriously consider implementing it?<=-=Sep 13 2017 11:42AM=-=>
If you wanted to vote, you can vote on this one instead:
It would have been nice if the Automated Transfer from Microsoft's defunct "Connect" website would have also transferred over the 146 votes.
Please everyone, vote on this!
Oracle has had this functionality baked in for years under the package: DBMS_Utility.Format_Call_Stack
And that not only includes the Call Stack, but Error Stack and Backtrace.
I'd also suggest the feature include the Starting and Stopping lines (i.e. the "statement_start_offset" and "statement_end_offset") within the Procedure/Trigger too, so we may dig out the Statement within the Logic that it branched from.
Using Context_Info or Session_Context will only help you find the end of the Call Stack and assumes Context_Info is not already used (which in my case it is) and that your database does never operates any Sprocs or Triggers in Parallel (which is not the case).
Debugging without a Call Stack requires modifying all of your code and slowing EVERYTHING down to log every invocation of a Sproc or Trigger.
When working in 3rd party databases this is simply NEVER an option because you should not change their existing logic.
Suggesting we use Profiler or Extended Events to run a non-stop Trace in Production is also a non-starter for tracking down bugs that only occur in a live environment or appear too intermittent to reproduce in a controlled environment.
I say "non-stop" because I would not only like to use this feature for fixing bugs, but for also logging the Call Stack when new Errors arise.
This would allow me to be as proactive as possible instead of waiting for complaints to pile up till we're asked to fix a bug and then hope someone can figure out how to reproduce the Error so we may then resort to turning on a focused Detailed Trace and then dig through the Trace Logs to try and make sense of the thousands of logged records from a super chatty 3rd Party Database that typically runs 20K RPC calls a minute when only one user is logged in.
Then we could better understand how to Avoid the Error or put in a Support Ticket to the 3rd Party PROVING their logic is broken and where to fix it - to speed up their patch development.
It has been my experience that Big 3rd Party Vendors will ignore requests and put them off unless you can prove the issue and point to exactly where the problem is.