Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146

    Unanswered: what is calling a stored proc?

    How can I find what is calling a stored proc in a database or on the server?


    Currently a job runs daily that calls a couple of DTS packages. Somewhere in this process a stored proc is being called that pumps out several emails. BUT nowhere in the DTS or job can I find any reference to the stored proc.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It could be a different stored proc that's calling your target proc.

    try this to search all sprocs in a given database for a given term:

    Code:
    DECLARE @SearchTerm VARCHAR(4000)
    SET @SearchTerm = 'SEARCH TERM GOES HERE'
    
    SELECT Object_NAME(id)
    FROM syscomments (NOLOCK)
    WHERE  id IN 
     (SELECT id FROM sysobjects (NOLOCK)                          
      WHERE xtype IN ('P', 'TF', 'FN', 'IF'))            
      AND 
     text LIKE '%' + @SearchTerm + '%' 
    ORDER BY Object_NAME(id)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    no luck, for the life of me i can't find what could be calling this.

    its working but i dont know how...

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by michelin man
    no luck, for the life of me i can't find what could be calling this.

    its working but i dont know how...
    Run SQL Profiler. Start the default profiler (called SQLProfilerStandard) and let it run for a few minutes (at a time when you believe that the proc is being called). End the trace and then search the textdata column for your proc name.

    Some notes:
    1. In the properties of the trace file, check the HostName option to see which machine the proc is being called from.
    2. If you are at all concerned about performance (profiler does place a small but noticeable load on the server), then limit your trace by filtering for just the db_id() where your proc is located.
    3. A trace file can get to be quite large if left unattended. Use some limits (such as time or file size).

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •