Wednesday 15 December 2010

RETRY mechanism with WHILE loop (tsql template)

This code demonstrates a RETRY mechanism with a WHILE loop.
The loop continues until the task succeeds or @MaxAttempts is reached.
If @MaxAttempts is reached and the task still fails, an email is sent

BEGIN

SET NOCOUNT ON

DECLARE @MaxAttempts INT
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)
DECLARE @ErrorMessage NVARCHAR(4000)  
DECLARE @ErrorSeverity INT  
DECLARE @EmailMessage VARCHAR(100)

SET @ErrorMessage = 'run_attempt'
SET @Counter = 0
SET @maxAttempts = 3


-- @ErrorMessage will be NULL after a successful execution of the program.
-- Checking for IS NOT NULL will mean the loop continues until success.

WHILE (@ErrorMessage IS NOT NULL) AND (@ErrorMessage NOT LIKE 'Warning: Null value%') AND (@Counter <= @MaxAttempts )

BEGIN   

 -- Increment counter and display run number to the screen
 SELECT @Counter = @Counter + 1 
 SET @CounterText = CONVERT(VARCHAR(30),@Counter,23)
 RAISERROR (@CounterText, 10, 1) WITH NOWAIT   
 
 
 BEGIN TRY
 
  -- Run the program you want to 'retry' 
  EXEC myschema.mystoredProc
  SELECT  @ErrorMessage = ERROR_MESSAGE() ,  @ErrorSeverity = ERROR_SEVERITY()  
 
 END TRY
 
 BEGIN CATCH

  IF @Counter = @MaxAttempts
  BEGIN
  
    SET @EmailMessage = 'mystoredProc  : Run Attempt : ' + CONVERT(VARCHAR(30),@Counter,23) + ' failed '

    EXEC msdb.dbo.sp_send_dbmail 
       @profile_name='SQL Profile'
     , @recipients='recipent@domain.com'
     , @body=@EmailMessage
     , @subject=@EmailMessage
     , @importance='High'
     
  END
 
 END CATCH

END  

END

No comments: