How to use Extended Events to report Query errors

Question:   We have an ETL job failing every night . The ETL job is using SQL Server Stored Procedures ,  so we're looking to be trace the errors reported in SQL Server, to give some clues on how to fix the problem?

Do you have an approach to monitor and save SQL Server  error messages ?

Answer: SQL Server Extended Events are your friend .  The great thing about Extended Events is the range of events that are monitored and then how customisable . For example , you may just want to monitor errors related to one stored procedure , or   you may want to record error messages for all Stored Procedures.

Here is an Extended Event definition , that will record SQL Server errors reported, and make certain fields available for viewing . The Exteded Event Session adds an extra step to pipe the output to an xel file ,  which you can execute queries to review the errors

--create Event Session 

CREATE EVENT SESSION [Queries_Failed] ON SERVER 
ADD EVENT sqlserver.error_reported 
(ACTION(sqlserver.client_app_name, sqlserver.client_hostname,  
sqlserver.database_name, sqlserver.sql_text, sqlserver.username) 
WHERE ([package0].[greater_than_int64]([severity], (10)))) 
ADD TARGET package0.event_file (SET 
filename = N'C:\temp\Queries_Failed.xel'
,metadatafile = N'C:\temp\Queries_Failed.xem'
,max_file_size = (5)
,max_rollover_files = (10))
WITH (STARTUP_STATE = ON)
GO

--start the event session. If you want to stop it  use STOP
ALTER EVENT SESSION [Queries_Failed] ON SERVER 
STATE = START;
GO


--Query to read the log file 




SELECT
[XML Data],
[XML Data].value('(/event[@name=''error_reported'']/@timestamp)[1]','DATETIME')AS [Timestamp],
[XML Data].value('(/event/action[@name=''database_name'']/value)[1]','varchar(max)')AS [Database],
[XML Data].value('(/event/data[@name=''message'']/value)[1]','varchar(max)')AS [Message],
[XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)')AS [Statement]
FROM
(SELECT 
OBJECT_NAME AS [Event], 
CONVERT(XML, event_data) AS [XML Data]
FROM 
sys.fn_xe_file_target_read_file
('C:\temp\Queries_Failed*.xel',NULL,NULL,NULL)) as FailedQueries;
GO