Re: Detect "calling" stored proc?

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 03/02/04 

Date: Tue, 2 Mar 2004 13:50:41 -0000

You can use SQL Profiler to find out by which procedure the second procedure
is called if you trace the SQL:StmtStarted or SQL:StmtCompleted events.

If you trace the following:
CREATE PROC usp_inner
AS
SELECT 1
GO
CREATE PROC usp_outer
AS
EXEC usp_inner
GO
EXEC usp_outer
GO
DROP PROC usp_outer, usp_inner
GO

You will see
-- usp_outer
EXEC usp_inner

-- usp_inner
SELECT 1

in the TextData column of your trace. The commented out bit is the calling
procedure. Even when you execute a variable procedure name, you can still
find out the calling procedure, because the calling procedure name will be
in the event before the first event that includes the called procedure's
name (like the example above) 

-- 
Jacco Schalkwijk
SQL Server MVP
"Louis" <Ludek@nowhereland> wrote in message
news:eTQsMmFAEHA.684@tk2msftngp13.phx.gbl...
> Hi,
>
> Is there any way to get an object ID for the the stored procedure or
trigger
> that called the currently executing stored proc?
> That is almost like @@PROCID will give me the ID of the currently
executing
> proc, but if @spCurrentProg was called from spCallingProg,
> or trigCallingTrig .. then I want the object id for that calling proc /
> trigger.
>
> This is all for debugging something on our system.
>
> Thanks
> Louis
>
>

Relevant Pages

  • [PROBLEM] 2.4.23-pre4 deadlocks while 2.4.22aa1 works fine
    ... Proc; keventd ... Trace; c011af8e ... Proc; kdeinit ... Proc; bash ...
    (Linux-Kernel)
  • Dynamically reassigning traces on functions
    ... I want to ensure that any trace I have setup on this exit procedure ... rename ExtendedExit __ExtendedExit ... proc as traces to catalog any traces that exist for procs/commands ... puts "defining catalog trace proc" ...
    (comp.lang.tcl)
  • Re: Frame alignment
    ... vwait $synth_ps ... create a proc that validates your input and returns a 1 or zero depending on if the data is valid or not. ... modify your input widgets to call the update_ui proc every time the data changes. ... Any time the value changes the trace will fire. ...
    (comp.lang.tcl)
  • Re: Getting Recent Search Terms
    ... I can't get your trace to work. ... > the SQL Profiler GUI in production environments. ... This script or any other profiler server-side script can be ... > "Hilary Cotter" wrote in message ...
    (microsoft.public.sqlserver.fulltext)
  • trace error
    ... But there is one thing I find curious and I would like to ask you, if there happens to be a part of the API (TCL or C) that I have overlooked. ... proc A { ... But if I want to make sure I am notified of every possible TCL error before the stack unwinds, I would have to wrap catch around every single proc in my program. ... In the same lines as "trace add variable" or "trace add command" can register procedures which are called before or after something happens to a variable or proc, "trace error callbackProc" would register the name of a proc to be called when an error occurres, before the stack unwinds, and would be executed in the same frame in which the error occurred. ...
    (comp.lang.tcl)
Loading