In questi giorni mi è capitata la necessità di investigare le cause di un comportamento anomalo che occorreva in modo casuale in un software di nostra produzione, e che si appoggia su un'istanza di database di Sql Server.

Il punto incriminato era una stored procedure, che veniva eseguita all'interno di una transazione molto complessa, con diversi punti del codice e diversi alltri oggetti di Sql Server richiamati alla bisogna.

La cosa più ovvia è stata mettere un sistema che permettesse di eseguire il log degli eventuali errori in modo da eseguire in modo efficace l'Handling SQL Server Errors.

Dopo un pò di ricerche ho pensato di porre il codice incriminato un blocco try/catch, in modo da poter analizzare nel seguito le cause di questa malfunzione.

Ignorantemente non conscevo la possibilità di utilizzare in una stored procedure i blocchi try/cath, al fine di monitorare eventuali errori.

Ecco l'estratto del codice che ho utilizzato.

CREATE PROCEDURE [dbo].[FantasticaStoredProcedure]
@Param1 int, @Param2 DateTime AS
BEGIN TRY

--Codice della stored procedure

END TRY

BEGIN CATCH
  DECLARE @ParamDetails VARCHAR(8000);
  SET @ParamDetails = ' @Param1 = ' + CAST(ISNULL(@PIdDoc, 'NULL') AS VARCHAR(100))
                    + ',@Param2 = ' + CAST(ISNULL(@Psegno, 'NULL') AS VARCHAR(100)) +
                    ERROR_MESSAGE()
  EXEC xp_logevent 60000,  @ParamDetails, error
  --ribatto errore
         DECLARE @ErrorMessage NVARCHAR(MAX);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;       
        SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
        
 RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
            );      

END CATCH

Nulla di eccezionale, ma occorre osservare alcune cosette. Per iniziare dove eseguire il dump dei dati relativi all'eventuale errrore occorso ?

La prima versione consisteva in un dump dei dati dell'errore in una tabella ad hoc: soluzione che subito si è rivelata sciocca, poichè essendo la stored procedure richiamata in transazione anche i dati ivi salvati erano oggetto di rollback transaction e quindi la tabella non veniva popolata con dati persistenti.

Osservo che in Sql Server non è possibile usare (almeno in modo facile) le sub-transaction o autonomous transaction, come in altri database, e che permette di creare transazioni autonome e indipendenti: l'unica soluzione facile che ho trovato è stata quella di salvare i dati sul log di sistema della macchina windoes ospitante, utilizzando l'istruzione xp_logevent.

Altra malfunzione verificata nella versione iniziale del codice sopra è stato quello di NON ribattere l'errore: infatti se alla fine non si richiama RAISEERROR la stored procedure non resituisce, correttamente, alcun errore al codice chiamante.

Il codice sopra dovrebbe funzionare correttamente !

Esistono anche altre soluzioni (utilizzo delle Table Variables o dell'istruzione RAISERROR WITH LOG), ma quella mostrata sopra mi è sembrata la più facile e intuitiva.

Spero che quanto esposto Vi possa essere utile.

Linkografia

xp_logevent (Transact-SQL)

sql server: inserting into a table within a transaction for logging error occurance?

Error and Transaction Handling in SQL Server