
-- create an event session that captures info for a specific error
-- for demostration it's an error that's easy to produce

-- list of events to capture for an error
select p.name [package name], xo.name, xo.description, xo.object_type
from sys.dm_xe_objects xo
join sys.dm_xe_packages p
on xo.package_guid = p.guid
where xo.object_type = 'event'
and xo.name like '%error%'
order by p.name, xo.name

-- create event for error
create event session errorsession on server
add event sqlserver.error_reported
(
action 
(
package0.callstack,    
sqlserver.session_id,   
sqlserver.sql_text     
)
where error = 547 and package0.counter <= 3  
)
add target package0.ring_buffer
WITH (max_dispatch_latency = 1 seconds)
go

-- start it up
alter event session errorsession on server state=start;
go

-- all registered sessions (active or not)
select * from sys.server_event_sessions
go

-- more info about my session
select ses.name, sese.name, sese.package, sese.predicate 
from sys.server_event_session_events sese
join sys.server_event_sessions ses 
on ses.event_session_id = sese.event_session_id
where ses.name = 'errorsession'
go

-- running event sessions
select * from sys.dm_xe_sessions
go

-- events captured for my session
select CAST(xet.target_data as xml) 
from sys.dm_xe_session_targets xet
join sys.dm_xe_sessions xe
on (xe.address = xet.event_session_address)
where xe.name = 'errorsession'
go

-- produce rows and columns
SELECT
	Data2.Results.value ('(data/.)[1]', 'int') AS ErrorNumber,
	Data2.Results.value ('(action/.)[2]', 'int') AS Session,
	Data2.Results.value ('(action/.)[3]', 'nvarchar(max)') AS SQLStatement
from
(
select CAST(xet.target_data as xml) as data
from sys.dm_xe_session_targets xet
join sys.dm_xe_sessions xe
on (xe.address = xet.event_session_address)
where xe.name = 'errorsession') events
cross apply Data.nodes ('//RingBufferTarget/event') 
  AS Data2 (Results)

-- must drop event to change action
-- cant add action to an existing event
alter event session errorsession on server
drop event sqlserver.error_reported

alter event session errorsession on server
add event sqlserver.error_reported
(
action 
(
package0.callstack,
sqlserver.create_dump_all_threads,
sqlserver.session_id, 
sqlserver.sql_text
)
where error = 547
and package0.counter <= 5
)
go

alter event session errorsession on server state=stop
go

drop event session errorsession on server
go
