February 5, 2015 at 11:45 pm
Hi,
I'm new in batch file commands.
I need a batch file to run all my sql scripts placed in a folder. Also need to log the execution result of each script in a text file.
Please help me
February 6, 2015 at 1:30 am
Just set the right variable values, make sure that path to SQLCMD is ok and should do the job
@ECHO OFF
SET SQLCMD="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
SET PATH="C:\path\to\sql\files\"
SET SERVER="Server\Instance"
SET DB="Database"
SET LOGIN="sa"
SET PASSWORD="pass"
SET OUTPUT="C:\OutputLog.txt"
CD %PATH%
ECHO %date% %time% > %OUTPUT%
for %%f in (*.sql) do (
%SQLCMD% -S %SERVER% -d %DB% -U %LOGIN% -P %PASSWORD% -i %%~f >> %OUTPUT%
)
OR if your scripts can be combined into one sql file and then executed we can skip the FOR loop and do something like this.
@ECHO OFF
SET SQLCMD="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
SET PATH="C:\path\to\sql\files\"
SET SERVER="Server\Instance"
SET DB="Database"
SET LOGIN="sa"
SET PASSWORD="pass"
SET INPUT="C:\AllScripts.sql"
SET OUTPUT="C:\OutputLog.txt"
COPY %PATH%\*.SQL %INPUT%
%SQLCMD% -S %SERVER% -d %DB% -U %LOGIN% -P %PASSWORD% -i %INPUT% -o %OUTPUT%
February 6, 2015 at 2:15 am
Emil Bialobrzeski (2/6/2015)
Just set the right variable values, make sure that path to SQLCMD is ok and should do the job
@ECHO OFF
SET SQLCMD="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
SET PATH="C:\path\to\sql\files\"
SET SERVER="Server\Instance"
SET DB="Database"
SET LOGIN="sa"
SET PASSWORD="pass"
SET OUTPUT="C:\OutputLog.txt"
CD %PATH%
ECHO %date% %time% > %OUTPUT%
for %%f in (*.sql) do (
%SQLCMD% -S %SERVER% -d %DB% -U %LOGIN% -P %PASSWORD% -i %%~f >> %OUTPUT%
)
OR if your scripts can be combined into one sql file and then executed we can skip the FOR loop and do something like this.
@ECHO OFF
SET SQLCMD="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
SET PATH="C:\path\to\sql\files\"
SET SERVER="Server\Instance"
SET DB="Database"
SET LOGIN="sa"
SET PASSWORD="pass"
SET INPUT="C:\AllScripts.sql"
SET OUTPUT="C:\OutputLog.txt"
COPY %PATH%\*.SQL %INPUT%
%SQLCMD% -S %SERVER% -d %DB% -U %LOGIN% -P %PASSWORD% -i %INPUT% -o %OUTPUT%
Thank you for your comments.It is working.But when error happens it is not displaying any error in the window. Need to check the output file is it Success or failure.
Is it possible to show if error happens show message, "Script execution failed check your log file for more details" and write the detailed error message in log file ?
February 6, 2015 at 2:47 am
I'm not sure if you can capture the error message itself but you can find out if there was an error or not.
Add -b to the SQLCMD
%SQLCMD% -S %SERVER% -d %DB% -U %LOGIN% -P %PASSWORD% -i %INPUT% -o %OUTPUT% -b
IF %ERRORLEVEL% == 1 (
ECHO THERE WAS AN ERROR - to file >> %OUTPUT%
ECHO THERE WAS AN ERROR - on screen)
Or if you are using the version with the loop
for %%f in (*.sql) do (
%SQLCMD% -S %SERVER% -d %DB% -U %LOGIN% -P %PASSWORD% -i %%~f -b >> %OUTPUT%
IF ERRORLEVEL == 1 (
ECHO THERE WAS AN ERROR - to file >> %OUTPUT%
ECHO THERE WAS AN ERROR - on screen)
)
February 6, 2015 at 3:15 am
Thank you that is working
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply