Extended Event to Capture Error Messages

CREATE EVENT SESSION CaptureErrors
ON SERVER
ADD EVENT sqlserver.error_reported(
	ACTION (
		sqlserver.sql_text,
		sqlserver.database_id,
		sqlserver.client_hostname,
		sqlserver.username,
		sqlserver.nt_username,
		sqlserver.tsql_stack
	)
WHERE 
severity > 10 and severity < 20  /*anything over20 is captured by System Health Event*/ )
ADD TARGET package0.asynchronous_file_target(
	SET FILENAME = N'U:\CaptureErrors.xel', 
	METADATAFILE = N'U:\CaptureErrors.xem'
)
WITH (max_dispatch_latency = 5 seconds);
GO

ALTER EVENT SESSION CaptureErrors ON SERVER
STATE = START
GO

DROP EVENT SESSION CaptureErrors ON SERVER
GO

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER EVENT SESSION CaptureErrors ON SERVER
STATE = STOP
GO

IF OBJECT_ID('tempdb..#ErrorData') IS NOT NULL
BEGIN
	DROP TABLE #ErrorData
END

CREATE TABLE #ErrorData(
	event_data xml
)

INSERT INTO #ErrorData (event_data)
SELECT  CAST(event_data as XML) 
FROM sys.fn_xe_file_target_read_file('U:\CaptureErrors*.xel', 'U:\CaptureErrors*.xem', NULL, NULL);

SELECT
	event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
	event_data.value('(event/@package)[1]', 'varchar(50)') AS package_name,
	event_data.value('(event/@id)[1]', 'varchar(50)') AS ID,
	event_data.value('(event/@version)[1]', 'varchar(50)') AS Version,
	DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
	event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
	event_data.value('(event/data[@name="error"]/value)[1]', 'int') as ErrorNumber,
	event_data.value('(event/data[@name="severity"]/value)[1]', 'smallint')  AS Severity,
	event_data.value('(event/data[@name="state"]/value)[1]', 'tinyint')  AS [State],
	event_data.value('(event/data[@name="user_defined"]/value)[1]', 'nvarchar(100)') as User_Defined,
	event_data.value('(event/data[@name="message"]/value)[1]', 'nvarchar(250)') as [Message],
	event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(4000)') as sql_text,
	event_data.value('(event/action[@name="database_id"]/value)[1]', 'int') as DatabaseID,
	event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(4000)') as client_hostname,
	event_data.value('(event/action[@name="nt_username"]/value)[1]', 'nvarchar(4000)') as nt_username,
	event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(4000)') as username,
	event_data.value('(event/action[@name="tsql_stack"]/value)[1]', 'nvarchar(4000)') as tsql_stack
FROM #ErrorData
ORDER BY [timestamp]