
-- capture the activity ID in an event session
-- you need "track_causality=on" to produce to activity ID

create event session get_activity on server
add event sqlserver.error_reported
(
action 
(
sqlserver.session_id,  -- add spid 
sqlserver.sql_text,     -- add query text
-- package0.attach_activity_id,  -- dont need to/cant add this action
-- package0.attach_activity_id_xfer,  -- dont need to/cant add this action
package0.collect_cpu_cycle_time,
package0.collect_system_time,
sqlos.task_elapsed_quantum,
sqlos.task_time
)
where error = 547
)
add target package0.ring_buffer
with ( track_causality = on )
go

alter event session get_activity on server state=start
go

-- Look at some of the output
SELECT CAST(xest.target_data AS XML) StatementData
	FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
WHERE xest.target_name = 'ring_buffer'
AND xes.name = 'get_activity';
GO

alter event session get_activity on server state=stop
go

drop event session get_activity on server
go