Ms Sql Extended Events procedure in procedre

Question

I have a procedure, which executes two other procedures, like this:

ALTER PROCEDURE [tmp].[Test] (@SessionId INT)

BEGIN
    EXEC [tmp].[Test1] @SessionId = @SessionId

    EXEC [tmp].[Test2] @SessionId = @SessionId
END

And then, executing [tmp].[Test] procedure, I would like to display the information from sys.fn_xe_file_target_read_file also for these internal procedures.

I created something like this:

SELECT event_name
    ,cpu_time
    ,duration
    ,logical_reads
    ,physical_reads
    ,writes
    ,statement
    ,plan_handle
    ,show_plan
    ,*
FROM (
    SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
        ,event_data.value('(event/action[@name="plan_handle"]/value)[1]', 'varchar(max)') AS plan_handle
        ,event_data.query('event/data[@name="showplan_xml"]/value/*') AS show_plan
        ,event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'int') AS cpu_time
        ,event_data.value('(event/data[@name="duration"]/value)[1]', 'int') AS duration
        ,event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'int') AS logical_reads
        ,event_data.value('(event/data[@name="physical_reads"]/value)[1]', 'int') AS physical_reads
        ,event_data.value('(event/data[@name="writes"]/value)[1]', 'int') AS writes
        ,event_data.value('(event/data[@name="statement"]/value)[1]', 'varchar(max)') AS statement
        ,event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
        ,*
    FROM (
        SELECT cast(event_data AS XML)
        FROM sys.fn_xe_file_target_read_file('E:\Pulpit\test_0_131645809565380000.xel', NULL, NULL, NULL)
        ) AS xevent(event_data)
    ) t

but it returns the result only for [tmp].[Test]. How can I get the result for [tmp].[Test2] and [tmp].[Test1] ?

score 0 · Answer 1 · edited Mar 04 '18 at 04:05

0

SELECT cast(event_data AS XML)
FROM sys.fn_xe_file_target_read_file('E:\Pulpit\test_0_131645809565380000.xel', NULL, NULL, NULL)

UNION

SELECT cast(event_data AS XML)
FROM sys.fn_xe_file_target_read_file('E:\Pulpit\test_1_131645809565380000.xel', NULL, NULL, NULL)

UNION

SELECT cast(event_data AS XML)
FROM sys.fn_xe_file_target_read_file('E:\Pulpit\test_2_131645809565380000.xel', NULL, NULL, NULL)

edited Mar 04 '18 at 04:05

Brien Foss

3,336
3
21
31

answered Mar 04 '18 at 00:03

seesharpguru

151
8

It doesn't work:The data type xml cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable. – resees111 Mar 04 '18 at 05:02

Ms Sql Extended Events procedure in procedre

1 Answers1