Από το SQL Server 2005 έχουμε στην διάθεση μας το TRY…CATCH με το οποίο μπορούμε να έχουμε ένα δομημένο exception handling και πραγματικά είμαστε όλοι ευχαριστημένοι με αυτό.
Παρόλο που αυτό κάνει εξαιρετικά την δουλειά του και πιάνει πραγματικά όλα τα run time exceptions υπάρχει κάτι που συμβαίνει και που οι περισσότεροι πιστεύουν ότι είναι limitation σε αυτό (κατά την γνώμη μου δεν είναι).
The problem
Για να δούμε όμως μια τέτοια περίπτωση ώστε να γίνει κατανοητό το πρόβλημα.
Έστω ότι θέλουμε να πάρουμε backup μια database και έχουμε γράψει λάθος το όνομα αυτής ή αυτή δεν υπάρχει για παράδειγμα:
BACKUP DATABASE db TO DISK = 'D:\TEMP\db_full.bak';
Εκτελώντας το command θα πάρουμε τα εξής exceptions:
Msg 911, Level 16, State 11, Line 1
Database 'db' does not exist. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Τα οποία είναι φυσικά αρκετά κατατοπιστικά
Ας δούμε ακόμα ένα παράδειγμα πάλι με backup αλλά αυτή την φορά έχουμε λάθος στο path το οποίο δεν υπάρχει:
BACKUP DATABASE demo TO DISK = 'D:\TEMP2\demo_full.bak';
Τα exceptions τα οποία θα πάρουμε και είναι σαφέστατα είναι τα παρακάτω:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:\temp2\demo_full.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Αν κάποιο από αυτά τα commands τα βάλουμε στο TRY…CATCH όπως στο παράδειγμα που ακολουθεί:
BEGIN TRY
BACKUP DATABASE db TO DISK = 'c:\temp2\demo_full.bak';
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS err_number,
ERROR_SEVERITY() AS err_severity,
ERROR_STATE() AS err_state,
ERROR_LINE() AS err_line,
ERROR_MESSAGE() AS err_message
END CATCH
Τότε οι ERROR functions που έχουμε μέσα στο CATCH θα μας γυρίσουν το εξής αποτέλεσμα:
Err_number = 3013, Err_severity = 16 , Err_state = 1 , Err_message = 'BACKUP DATABASE is terminating abnormally.'
Από αυτό βέβαια δεν είναι εύκολο να καταλάβουμε το πραγματικό exception.
Το "πρόβλημα" δεν είναι ακριβώς στο TRY…CATCH αλλά κυρίως στις ERROR functions που επιστρέφουν μόνο το τελευταίο exception.
Όσοι έχουμε ασχοληθεί με το .ΝΕΤ και το αντίστοιχο try…catch σε αυτό ξέρουμε ότι υπάρχει η SQLException class (System.Data.SqlClient) στην οποία υπάρχει το Errors property με το οποίο μπορούμε να πάρουμε όλα τα λάθη που έχουν ενεργοποιηθεί και φυσικά μπορούμε με αυτό τον τρόπο να καταλάβουμε πολλά περισσότερα για το exception.
Θα θέλαμε να υπάρχει κάτι τέτοιο ενσωματωμένο στο SQL Server αλλά ακόμα δυστυχώς δεν υπάρχει (άλλα πιέζουμε product group να βρει χρόνο να το κάνει).
Workaround
Μέχρι αυτό να υλοποιηθεί και με την προϋπόθεση ότι είμαστε σε SQL Server 2008 R2 και πάνω μπορούμε να χρησιμοποιήσουμε τα Extended Events.
Στο σημείο αυτό θα πρέπει τονίσω ότι κάτι τέτοιο δεν χρειάζεται να το κάνουμε για εργασίες που επιστρέφουν ένα και μόνο ένα exception όπως τα INSERT/UPDATE/DELETE. Συνήθως εργασίες που επιστρέφουν περισσότερα από ένα exceptions είναι οι BACKUP/RESTORE/DBCC COMMANDS.
Create Extended Event Session
Αρχικά θα πρέπει να φτιάξουμε ένα Extended Event Session στο οποίο να καταγράφουμε τα SQL Server exception και αυτό μπορεί να γίνει με το event sqlserver.error_reported και στο οποίο προσθέτουμε να καταγράφεται το sessionid και το sql command text αλλά επειδή δεν θέλουμε για όλα τα άλλα session κάνουμε filtering μόνο για το τρέχον session.
Επειδή θέλω και το όνομα να περιέχει το sessionid αλλά επειδή το @spid function δεν μπορεί να μπει στο filtering χρησιμοποιώ dynamic sql. Για την ευκολία μου όλο αυτό το έχω κάνει stored procedure η οποία είναι η παρακάτω.
CREATE proc [dbo].[CreateXE_ErrTrapSession]
as
begin
if exists (select * from sys.dm_xe_sessions where name = N'ErrorTrappingSession'+cast(@@spid as nvarchar(6)))
begin
exec master.[dbo].[DropXE_ErrTrapSession];
end
declare @XECreateCmd nvarchar(max) = N'CREATE EVENT SESSION [ErrorTrappingSession@SPID]
ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION(
sqlserver.session_id,
sqlserver.sql_text
)
WHERE [package0].[not_equal_unicode_string]([message],N'''')
AND [severity]>(10)
AND [sqlserver].[session_id]=@SPID
)
ADD TARGET package0.ring_buffer
WITH (
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_MEMORY=4096 KB,
MAX_DISPATCH_LATENCY=1 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=ON,
STARTUP_STATE=OFF
);
ALTER EVENT SESSION [ErrorTrappingSession@SPID] ON SERVER STATE=START;'
set @XECreateCmd = REPLACE(@XECreateCmd,'@SPID',cast(@@SPID as nvarchar(6)))
exec (@XECreateCmd)
end
GO
Drop Extended Event Session
Επειδή δεν πρέπει να μένουν "σκουπίδια" πρέπει να γίνεται drop το extended event και επειδή όπως παραπάνω είπα αυτό περιέχει το sessionid στο name πάλι κάνω την συγκεκριμένη διαδικασία με dynamic sql και το έχω βάλει σε μια stored procedure που είναι η παρακάτω
CREATE proc [dbo].[DropXE_ErrTrapSession]
as
begin
if exists (select * from sys.dm_xe_sessions where name = N'ErrorTrappingSession'+cast(@@spid as nvarchar(6)))
begin
declare @XECmd nvarchar(max) = N'ALTER EVENT SESSION [ErrorTrappingSession@SPID] ON SERVER STATE=STOP;
DROP EVENT SESSION [ErrorTrappingSession@SPID] ON SERVER;'
set @XECmd = REPLACE(@XECmd,'@SPID',cast(@@SPID as nvarchar(6)))
exec (@XECmd)
end
end
GO
Parse Errors
Τέλος θέλω μέσα στο CATCH block να μπορώ να διαβάσω τα exceptions και για αυτό το λόγο φτιάχνω μια stored procedure που διαβάζει τo XE Event Session και επειδή ήθελα να στέλνω email έχω φτιάξει αυτά να γίνονται σαν html table. Μπορεί εύκολα κάποιος να το αλλάξει και να το κάνει να φέρνει τα exceptions με την μορφή που θέλει.
CREATE proc [dbo].[ParceXE_ErrTrapSession] @TableBodyForEmail nvarchar(max)=N'' output
as
begin
DECLARE @XEData XML
SELECT @XEData = 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 = 'ErrorTrappingSession'+cast(@@spid as nvarchar(6));
IF @XEData.value('(/RingBufferTarget/@totalEventsProcessed)[1]', 'INT') = 0
BEGIN
WAITFOR DELAY '00:00:02';
SELECT @XEData = 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 = 'ErrorTrappingSession53' --+cast(@@spid as nvarchar(6));
END;
DECLARE @TableBody NVARCHAR(MAX) = '<table border="1">' +
'<tr>' +
'<th>Error Number</th>' +
'<th>Severity</th>' +
'<th>State</th>' +
'<th>Message</th>' +
'<th>Sql Text</th>' +
'<th>Session ID</th>' +
'</tr>';
with tr
as
(
SELECT
x.c.value(N'(data[@name="error_number"]/value)[1]', N'NVARCHAR(MAX)') AS ErrorNumber,
x.c.value(N'(data[@name="severity"]/value)[1]', N'NVARCHAR(MAX)') AS Severity,
x.c.value(N'(data[@name="state"]/value)[1]', N'NVARCHAR(MAX)') AS [State],
x.c.value(N'(data[@name="message"]/value)[1]', N'NVARCHAR(MAX)') AS [Message],
x.c.value(N'(action[@name="sql_text"]/value)[1]', N'NVARCHAR(MAX)') AS SqlText,
x.c.value(N'(action[@name="session_id"]/value)[1]', N'NVARCHAR(MAX)') AS SessionId
FROM @XEData.nodes('//RingBufferTarget/event') AS x(c)
)
select @TableBody+=(select
ErrorNumber as td,
Severity as td,
[State] as td,
[Message] as td,
SqlText as td,
SessionId as td
from tr
for xml auto , elements
)
set @TableBodyForEmail = @TableBody+N'</table>'
end
GO
All together
Όλα αυτά μαζί μου δίνουν την παρακάτω υλοποίηση και εγώ πλέον λαμβάνω ένα email που περιέχει αναλυτικά όσες πληροφορίες χρειάζομαι.
EXEC [dbo].[CreateXE_ErrTrapSession];
BEGIN TRY
BACKUP DATABASE db TO DISK = 'c:\temp2\demo_full.bak';
END TRY
BEGIN CATCH
DECLARE @TableBodyForEmail nvarchar(max)=N'';
EXEC master.[dbo].[ParceXE_ErrTrapSession] @TableBodyForEmail output
declare @subject nvarchar(255)= N'SUBJECT'
declare @body nvarchar(max)='';
set @body =N'<h2>Errors</h2>' + @TableBodyForEmail ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMAILPROFILE',
@recipients = 'sender@company.com',
@subject = @subject,
@body=@body,
@body_format='HTML',
@importance ='High',
@attach_query_result_as_file = 0;
END CATCH
EXEC [dbo].[DropXE_ErrTrapSession]
//antonch