Lesson Learned #226: Using a connectivity Retry-Logic with SQLCMD command line

Today, We have been working on a service request that our customer needs to implement a connection retry-logic using SQLCMD. Following, I would like to share some ideas for this implementation. 

The first thing that we need to know if we might have two issues using SQLCMD connecting to the database and executing the query, for this reason, I'm going to run the following statement saving it in a .BAT file:

@echo off

SET /A "index = 1"
SET /A "count = 10"
SET /A "waitfor = 5"
SET /A "size = 0"

:while
if %index% leq %count% (
   echo ------------------------------------------------------------------------
   echo Retry-Logic - Attempt Number %index%

   if exist C:\PERFCHECKER\OutPut_%index%.TXT ( 
       DEL /f /q C:\PERFCHECKER\OutPut_%index%.TXT 
	   echo Retry-Logic - Deleted file C:\PERFCHECKER\OutPut_%index%.TXT 
	   timeout 1 /nobreak
	   )
   if exist C:\PERFCHECKER\OutPut_%index%.LOG ( 
       DEL /f /q C:\PERFCHECKER\OutPut_%index%.LOG
	   echo Retry-Logic - Deleted file C:\PERFCHECKER\OutPut_%index%.LOG 
	   timeout 1 /nobreak
	   )
   echo Retry-Logic - Executing SQLCMD
     sqlcmd -S tcp:servername.database.windows.net,1433 -U username -P password -l60 -t3600 -d databasename -b -Q "EXEC ExecuteThis" -o C:\PERFCHECKER\Output_%index%.TXT
   echo Retry-Logic - Executed SQLCMD Error Level Result %errorlevel%   
   
   if %errorlevel% EQU 0 (
   	 echo Retry-Logic - Checking if the file exist C:\PERFCHECKER\OutPut_%index%.TXT 
     IF exist C:\PERFCHECKER\OutPut_%index%.TXT (
        echo Retry-Logic - Reading file C:\PERFCHECKER\OutPut_%index%.TXT File result %errorlevel%	 	  
        FINDSTR "ProcessFinishedWithSuccess" C:\PERFCHECKER\OutPut_%index%.TXT >C:\PERFCHECKER\Output_%index%.LOG
         if %errorlevel% EQU 0 (
	       echo Retry-Logic - Checking if the file exist	C:\PERFCHECKER\OutPut_%index%.LOG
	       IF exist C:\PERFCHECKER\OutPut_%index%.LOG (
     	     FOR %%A IN (C:\PERFCHECKER\Output_%index%.LOG) DO set size=%%~zA
	     	 echo Retry-Logic - File C:\PERFCHECKER\OutPut_%index%.log Size is %size%
		      if %size% neq 0 (
		        set /p Build=<C:\PERFCHECKER\Output_%index%.LOG
                set errorRetryLogic=%Build:~0,26%
                echo Retry-Logic - : Build  %Build%
                echo Retry-Logic - : Return %errorRetryLogic% 
                if "%errorRetryLogic%" == "ProcessFinishedWithSuccess" (
		           echo Retry-Logic - Finished
		           goto :end)
		      )    
	       )	
	    )  
	 ) 
	)  
     SET /A "waitfor = waitfor + (index*2)" 
	 timeout %waitfor% /nobreak
	 SET /A "index = index + 1"  
	 goto :while
  )
:end

• My retry-logic will be:
  • Retry the operation 10 times using the variable index and count. 
  • Using a temporal file placed on a folder called C:\PERFCHECKER\Output_nnnn.TXT (where nnnn will be the number of retries) to save the results of the SQLCMD execution. 
  • If the %ErrorLevel% of SQLCMD reports a value different than 0, this process will be retried. 
  • Search using the content generated by SQLCMD execution in the C:\PERFCHECKER\Output_nnnn.TXT if we have a text called "ProcessFinishedWithSuccess" saving the results in the temporal file C:\PERFCHECKER\Output_nnnn.LOG
  • If the file C:\PERFCHECKER\Output_nnnn.LOG exists and the size is different than 0 read the data and check if the result is "ProcessFinishedWithSuccess" the process will be finished. 
  • If not or if we have an issue during the execution, the script will continue working with the process retrying the operation after some seconds. 

As an example, I wrote the following TSQL that contains the store procedure that I'm using to capture the results. The last line contains the text that I'm using to know if the execution was completed correctly.

CREATE PROCEDURE ExecuteThis
AS
BEGIN
    SET NOCOUNT ON
	SELECT TOP 60 * FROM DEMO
	SELECT 'ProcessFinishedWithSuccess'
END

Enjoy!