This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
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"
: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!
