Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2009
    Posts
    51

    Unanswered: How to log procedure calls within procedure in SQL profiler -

    Hi

    I have main stored procedures that call other stored procedures within main procedures but profiler only logs the main procedure calls.

    I need all the procedure calls logged in profiler.

    Help will be appriciated.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Does this 1 sproc call all the other sprocs?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2009
    Posts
    51

    Yes ...that's right

    Something like below.

    Stored Prcedure A
    (
    ...
    ...
    Exec Strored Procedure B
    ...
    ...
    )

    Strored Prcedure B
    (

    .....
    ....
    Exec Stored Prcedure C
    ...
    ...
    )

    Thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    what exactly do you want to log?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2009
    Posts
    51

    I need to log all the calls to procedures A, B, C

    First of all thanks for your time.
    I need to log all calls to procedure A, B and C
    Currently there profiler logs only one call that is proc A

    The reason is there are many hundreds of procedures are called just not only 3 and the data manipulation is huge.

    Is there any way?

    Thanks again.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ok, well that let's my idea out


    See, when I have our team build sprocs, one of the standards I use is to call a logging sproc that tells me who, when, the host and the length of the transaction

    That's in the exit routine of every sproc

    But that doesn't sound like an option for you

    In any case...EVERY TSQL that is executed should show up in profiler

    So your saying you don't see them?

    That's odd

    Are you writing the profile results to a table or a file?

    And what options are you using?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2009
    Posts
    51

    Just stored proc and t-sql.

    Hi Brett,

    Just stored proc and t-sql.
    I dont do any sort of logging.
    Its is odd as you said.

    Thanks
    Bill

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    do a simple test

    create a sproc that calls a sproc, strat profiler, then call the sproc

    Tell us what you see
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2009
    Posts
    51

    confirming the behaviour logged only one call

    I created two procedures as follows

    --------------------****---------------------
    CREATE PROCEDURE [dbo].[A]

    AS

    print 'A'

    exec dbo.B
    GO
    --------------------****---------------------
    CREATE PROCEDURE [dbo].[B]


    AS


    print 'B'
    GO
    --------------------****---------------------

    query analyser output

    OUTPUT
    ------
    A

    (1 row(s) affected)

    OUTPUT
    ------
    B

    (1 row(s) affected)


    Profiler log
    EventClass| TextData | AppName| LoginName
    SQL:BatchCompleted| exec A| Query Analyser| sa

  10. #10
    Join Date
    Feb 2009
    Posts
    51
    Does anybody know how any trick?
    Will be appriciated.

  11. #11
    Join Date
    Feb 2009
    Posts
    51
    The following settings in SQL Profiler is quite similar to what I wanted.

    Stored Procedures
    |
    |__ SP: StmtStarting

    TSQL
    |
    |__ SQL: BatchCompleted

    Regards

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I gotta test it it out, but not now....it's cocktail time

    Oh, wait, what time is it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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