====== Catching multiple errors ======

The error values available as functions in the scope of CATCH block only return information about the //last// error raised, but sometimes (e.g. if a database restore fails because the disk is full) several errors are thrown. 

Extended events can be used to trap //all// errors emerging from a SQL statement. This query thanks to [[http://dba.stackexchange.com/questions/53966/sql-servers-backup-database-how-to-access-the-original-exception|TToni]].

<code tsql>CREATE EVENT SESSION ErrorCatcher ON SERVER
ADD EVENT sqlserver.error_reported (WHERE severity >= 16)
ADD TARGET package0.ring_buffer
WITH (
     EVENT_RETENTION_MODE = NO_EVENT_LOSS,
     MAX_DISPATCH_LATENCY = 1 SECONDS
     )

ALTER EVENT SESSION ErrorCatcher ON SERVER STATE = START

BEGIN TRY
    BACKUP DATABASE MyDatabase
        TO DISK = 'C:\MyDatabase.bak'
        WITH INIT, 
        NAME = 'MyDatabase-FullBackup',
        STATS = 5;
END TRY
BEGIN CATCH
    DECLARE @target_data XML

    WAITFOR DELAY '00:00:01' -- Wait for the MAX_DISPATCH_LATENCY time

    SELECT @target_data = CAST(xet.target_data AS xml)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
       ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'ErrorCatcher'

    SELECT 
        n.value('(@name)[1]', 'varchar(50)') AS event_name,
        n.value('(@package)[1]', 'varchar(50)') AS package_name,
        n.value('(@id)[1]', 'int') AS id,
        n.value('(@version)[1]', 'int') AS version,
        DATEADD(hh, 
                DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
                n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
        n.value('(data[@name="error"]/value)[1]', 'int') as error,
        n.value('(data[@name="severity"]/value)[1]', 'int') as severity,
        n.value('(data[@name="duration"]/value)[1]', 'int') as state,
        n.value('(data[@name="user_defined"]/value)[1]', 'varchar(5)') as user_defined,
        n.value('(data[@name="message"]/value)[1]', 'varchar(max)') as message
    FROM @target_data.nodes('RingBufferTarget/event') AS q(n);
END CATCH    

ALTER EVENT SESSION ErrorCatcher ON SERVER STATE = STOP

DROP EVENT SESSION ErrorCatcher ON SERVER
</code>

{{tag> tsql admin extended_events errors}}