executing sql files in for loop, error is not recorded and not stoping after error

Question

in below batch script, I expect all scripts were kept at c:\temp and they will execute one by one and reord all successful/error records in log file, if any error occours then it should stop what syntex going wrong here, one more I kept this batch script and all sql scripts in one folder only

@Echo Off
FOR /f %%i IN ('DIR C:\temp\*.Sql /B') do call :RunScript %%i
GOTO :END

:RunScript
Echo Executing %1
@set SName=someservername
@set DbName=somedbname
@set path=C:\temp

echo sqlcmd -S %SName% -d %DbName% -i %1 -o "%path%\log.txt" 
if not %errorlevel%==0 exit
Echo Completed %1

:END

Just remembered: when you `@set path=C:\temp` you cause shell not to find `sqlcmd.exe`, unless it exists in current directory or in `c:\temp`! — LS_ᴅᴇᴠ, Aug 21 '13 at 11:13

score 0 · Answer 1 · answered Aug 20 '13 at 14:47

0

You need goto :eof at end of subroutine (after "Echo Completed %1"); otherwise you are not returning from called subroutine. Your code also assumes no spaces in filenames. Is that the case?

answered Aug 20 '13 at 14:47

RGuggisberg

4,630
2
18
27

That's not correct. Reaching end of batch file is same as `GOTO :EOF`. – LS_ᴅᴇᴠ Aug 21 '13 at 09:40
That's assuming that the OP has shown ALL of the code. It is still preferrable to add the GOTO :eof in case more code is added later. – RGuggisberg Aug 21 '13 at 10:58
I would do that also. However, `:END` means much! – LS_ᴅᴇᴠ Aug 21 '13 at 11:07
still log file is getting blank – Hemu Aug 23 '13 at 13:32

LS_ᴅᴇᴠ · Answer 2 · 2013-08-23T13:33:29.683

0

Ok, I will point errors first, solution after. I don't know sqlcmd, but I'm checking documentation.

FOR /f %%i IN ('DIR C:\temp\*.Sql /B') do call :RunScript %%i

May be a problem if filenames have spaces within

@set path=C:\temp

As already told, avoid using system variables. SQLCMD may rely on %path%!

echo sqlcmd -S %SName% -d %DbName% -i %1 -o "%path%\log.txt"

echo cause command line to be displayed, not executed! From documentation, -oparameter implies "A file of the same name from a prior sqlcmd session will be overwritten". So, each time you run sqlcmd, previous log is overwritten.

if not %errorlevel%==0 exit

Use exit /b or command line interpreter is stopped, which may break flow when using nested batches.

My suggestion:

FOR /F "TOKENS=*" %%i IN ('DIR C:\temp\*.Sql /B') do call :RunScript "%%i"
GOTO :EOF

:RunScript
Echo Executing %1
SQLCMD -S someservername -d somedbname -i %1 -b -m-1 1>> "C:\temp\log.txt"
IF %ERRORLEVEL%==0 EXIT /B
Echo Completed %1

EDIT: typo correction

EDIT: correcting switches: -o stands for output. Better use -m and catch stdout.

edited Aug 23 '13 at 13:33

answered Aug 21 '13 at 10:05

LS_ᴅᴇᴠ

10,823
1
23
46

This script is much improved is doing good but log.txt created as blank, I expect result in it – Hemu Aug 23 '13 at 11:46
I have 5-10 commnads and log file is getting blank, I can't understand commands are executed or not ? – Hemu Aug 23 '13 at 12:50
You MUST read documentation. `-o` stores output. And this should only be SQL queries results. – LS_ᴅᴇᴠ Aug 23 '13 at 13:37
LS_dev, I was done as per your mofdified script, still getting same error, log file is generated but it is blank – Hemu Aug 26 '13 at 05:08

score 0 · Answer 3 · answered Feb 14 '14 at 05:27

for %%G in (*.sql) do (sqlcmd /S %sqlhost% /d %sqldbname% -E -b -i "%%G" >> output.txt if ERRORLEVEL 1 exit)

Above code will loop through all the *.sql in a folder. If error encountered in any of the script , error will get logged in the output.txt file and it will stop the batch process immediately.Create a batch file with the above two lines of code and put it in your folder(C:\temp)

executing sql files in for loop, error is not recorded and not stoping after error

3 Answers3