Retry OSQL command if deadlock occurs in FOR loop

Question

So I am working on my first batch script that utilizes a OSQL query. Here is my current code:

setlocal enableDelayedExpansion
FOR /F %%G in (names.txt) DO  (
    :RESTARTER
    osql -b -D PCRServer -r -h-1 -s "," -i %%G.sql -o C:\SQLQurey\out\%%G.txt -U     SOMEUSER -P SOMEPASS
    if !ERRORLEVEL! NEQ 0 goto RESTARTER

)
endlocal

The idea is that names.txt has a list of sql queries to run, which then spits the output to a file with the same name, which can then be concatenated into a master output later on.

What I am running into problems with is error checking, specifically deadlocks. What I want to happen when a deadlock is encountered is to re-run the query that failed, then continue down the list.

My thoughts were that with the errorlevel check and goto, it would do what I want. Instead, when it jumps back up to :RESTARTER, the value of %%G is lost and I get a permanent error loop.

I have tried setting another variable to the value of %%G outside of restarter...and while this works, once the query is executed successfully, it drops out of the original FOR loop and stops.

So, my question is, how do I tell the script to retry the last failed command and continue the FOR loop until the end of names.txt is reached?

Edit: Okay, so I was able to make it work by putting the actual osql command in a separate batch file, then calling that batch from the loop and passing %%G as a parameter. However, I'd still be interested in knowing if there is a way to do this without separate scripts.

Answer 1

Here's my suggestion. Change MaxTrys to some number you like. After you make this work you might want to consider using SQLCMD instead of OSQL. OSQL has been deprecated. Of course you will need to use OSQL if you are using SQL Server 2000.

@ECHO OFF
SET MaxTrys=10
FOR /F %%G in (names.txt) DO  (
   ECHO.G=%%G - Remove this line later if you don't need to see
   CALL :DoSQL "%%G"
)
PAUSE & REM Remove this line later
GOTO :eof

:DoSQL
FOR /L %%H in (1,1,%MaxTrys%) DO (
   osql -b -D PCRServer -r -h-1 -s "," -i %~1.sql -o C:\SQLQuery\out\%~1.txt -U SOMEUSER -P SOMEPASS
   IF NOT ERRORLEVEL 1 GOTO :eof
)
ECHO.ERROR.  :DoSQL Failed %MaxTrys% times.
GOTO :eof