Batch script for executing sql files that does nothing

Question

I've got the following batch script for executing sql scripts contained in many files, using sqlcmd utility:

@echo off
setlocal enabledelayedexpansion
set /p servername=Enter DB Servername :
set /p dbname=Enter Database Name :
set /p spath=Enter Script Path :
set /p usr=Enter the username :
set /p pw=Enter the password :
set hr=%time:~0,2%
if "%hr:~0,1%" equ " " set hr=0%hr:~1,1%
set logfilepath= %spath%\output_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hr%%time:~3,2%%time:~6,2%.log
set cmd='dir "%spath%\*.sql" /b'
FOR /f "delims=" %%f IN (%cmd%) DO (
    echo ******PROCESSING %%f FILE******
    echo ******PROCESSING %%f FILE****** >> %logfilepath%
    SQLCMD -S%servername% -U%usr% -P%pw% -d%dbname% -s"  -i%%f -b >> %logfilepath%
    IF !ERRORLEVEL! NEQ 0 GOTO :OnError
)
GOTO :Success

When I run this script a prompt with "1>" will wait for command. I can understand what is going wrong.

Thank you very much

RGuggisberg · Accepted Answer · 2016-04-28 14:16:17Z

2

You are getting SQLCMD started and not doing what you desired. For starters I would change this line

    SQLCMD -S%servername% -U%usr% -P%pw% -d%dbname% -s"  -i%%f -b >> %logfilepath%

to

SQLCMD -S %servername% -U %usr% -P "%pw%" -d%dbname% -s ^"  -i "%%f" -b >> "%logfilepath%"

Not sure if spaces I added are required, but easier to read
Use quotes in case there are poison characters or spaces in password, filename, and path
Escape the column separator ("). This is probably what is causing your problem.

answered Apr 28, 2016 at 14:16

RGuggisberg

4,7802 gold badges21 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RGuggisberg Over a year ago

You are welcome. FYI, I meant to add a space after -d% too. Also, beware that checking the error level after SQLCMD is not doing much for you. It only verifies that you were able to launch SQLCMD... it does not provide any result as to the status of the SQL commands run by SQLCMD. For that you need to parse the contents of the log file that you directed the results to.

Collectives™ on Stack Overflow

Batch script for executing sql files that does nothing

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related