Detect "calling" stored proc?

Detect "calling" stored proc?

Post by Loui » Wed, 03 Mar 2004 22:20:20



Hi,

Is there any way to get an object ID for the the stored procedure or trigger
that called the currently executing stored proc?


or trigCallingTrig .. then I want the object id for that calling proc /
trigger.

This is all for debugging something on our system.

Thanks
Louis

 
 
 

Detect "calling" stored proc?

Post by Jacco Schalkwij » Wed, 03 Mar 2004 22:50:41


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


> Hi,

> Is there any way to get an object ID for the the stored procedure or
trigger
> that called the currently executing stored proc?

executing

> or trigCallingTrig .. then I want the object id for that calling proc /
> trigger.

> This is all for debugging something on our system.

> Thanks
> Louis


 
 
 

Detect "calling" stored proc?

Post by Brian Mora » Wed, 03 Mar 2004 23:00:28


It's easy to do if you're using Profiler....

--

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com


> Hi,

> Is there any way to get an object ID for the the stored procedure or
trigger
> that called the currently executing stored proc?

executing

> or trigCallingTrig .. then I want the object id for that calling proc /
> trigger.

> This is all for debugging something on our system.

> Thanks
> Louis

 
 
 

1. Stored Proc using "like"??

Can anyone help point me in the right direction?  I can't
figure out why my stored proc isn't returning the correct
results when I call it from an ASP page.  Thanks in
advance!!!!!!!

SP:

(50) AS



select qcnproduct.productid, qcnproduct.productname,
qcnproduct.prodpicture,
qcnproditem.qcnprice,
qcnproditem.supplycount,qcnproditem.typeid,
qcnsupplytype.typedesc
from qcnproduct
        inner join qcnproditem
                inner join qcnsupplytype on
qcnproditem.typeid = qcnsupplytype.typeid
        on qcnproduct.productid = qcnproditem.productid



order by qcnproduct.productname, qcnproduct.productid,
qcnproditem.supplycount

ASP Page:
lcSearchPhrase = Request.QueryString("searchfor")
lcSearchPhrase = trim(lcSearchPhrase)
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Session("strConn")
Set oCmd = Server.CreateObject("ADODB.Command")
set oCmd.ActiveConnection = oConn
oCmd.CommandText = "spSearchProducts"
oCmd.commandtype = AdCmdStoredProc
oCmd.Parameters.Append oCmd.CreateParameter


set oRsSR = oCmd.Execute

Thanks,
-Bruce

2. search in any part of field

3. Execute "KILL" from stored proc

4. How to speed up TDataSet.Next

5. "Errors Occured" executing a store proc

6. Processor 100%

7. Stored Proc - "Optional" parameters

8. ODBC32.txt module for ODBC 3.0

9. Executing "sp_start_job" stored proc

10. Making Stored Proc Resultset "Editable"

11. "Errors Occured" executing a store proc

12. max of ("...","...","..")

13. Detect "Enter" keycode in data