/*

       -- ignore errors 5701 and 5793

       5701 = Changed database context to '{user db name}'.

       5703 = Changed language setting to us_english.

*/

CREATE EVENT SESSION azure_monitor ON DATABASE

       ADD EVENT sqlserver.error_reported(

       ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id, sqlserver.sql_text,

                    sqlserver.username)

             WHERE error_number <> 5701 and error_number <> 5703

                    )

       ADD TARGET package0.ring_buffer

       (SET max_memory = 5000, max_events_limit = 100 );

 

ALTER EVENT SESSION azure_monitor

ON DATABASE

STATE = START;

 

-- Some errors occurred

 

-- Query captured errors

if object_id('tempdb..#tmp_bob') is not null drop table #tmp_bob

 

SELECT CAST(target_data AS XML) as target_data into #tmp_bob

FROM sys.dm_xe_database_sessions AS s

JOIN sys.dm_xe_database_session_targets AS t

ON t.event_session_address = s.address

WHERE s.name = N'azure_monitor';

 

-- Stopping

ALTER EVENT SESSION azure_monitor

ON DATABASE

STATE = stop;

 

ALTER EVENT SESSION  azure_monitor

ON DATABASE

DROP TARGET package0.ring_buffer;

 

DROP EVENT SESSION azure_monitor

ON DATABASE;

select * from #tmp_bob

 

SELECT

 xed.event_data.value('(@timestamp)[1]', 'datetime') AS EventTime

,xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(56)') AS username

,xed.event_data.value('(action[@name="client_hostname"]/value)[1]', 'varchar(56)') AS client_hostname

,xed.event_data.value('(data[@name="error_number"]/value)[1]', 'int') AS [error_number]

,xed.event_data.value('(data[@name="message"]/value)[1]', 'varchar(256)') AS [message]

,xed.event_data.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text

FROM #tmp_bob

  CROSS APPLY target_data.nodes('//RingBufferTarget/event') AS xed (event_data);