Extended Event collect sql text executed

  • Dear Coallegues,

    I am trying to configure an extended event session in order to capture parameters executed with a specific stored procedures in a specific database, without results:

    It is not possible use Profiler

    Please help, thanks for any idea

    CREATE EVENT SESSION [9999] ON SERVER

    ADD EVENT sqlserver.rpc_completed

    (

    ACTION

    (

    sqlserver.database_id,

    sqlserver.session_id,

    sqlserver.username,

    sqlserver.client_hostname,

    sqlserver.sql_text,

    sqlserver.tsql_stack

    )

    WHERE (sqlserver.database_id = 24) )

    ADD TARGET package0.asynchronous_file_target

    (

    SET FILENAME = N'd:\test\299650v5.xel',

    METADATAFILE = N'd:\test\299650v5.xem'

    )

    WITH (MAX_MEMORY=2048 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

    MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,

    MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF

    );

  • Profiler is of course a really bad idea, but why would a server side Trace be impossible?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Try adding the module_end event to your event session. I believe you need to include that to get the parameters.

    Sue

  • Thanks for your responses,

    but it appears that in SQL Server 2008 (r2), is not possible to capture  store procedures parameters, I have tryed with the events commented above and it brings the name but not the parameters.

    Again, thanks for your ideas and effort.

  • Yeah, all I get for tsql_text is "Unable to retrieve SQL text".

    Extended Events in SQL 2008 was a little half-baked, and things like capturing what is executed definitely works better with Trace in that version. (Well, I tend to think that it works better in any version given how much easier Trace is to use, but that is another story.)

    So these appear to be the choices: Trace ...or upgrade, which is overdue anyway.

     

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog wrote:

    (Well, I tend to think that it works better in any version given how much easier Trace is to use, but that is another story.)

    It's too bad that we can only give one "LIKE" to a post or I'd give you a million on that statement. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply