I have about 100 scripts that needs to be executed on SSMS. How can i execute them as a batch instead of executing each individually.
I tried using this but it didn't work.
@echo off
ECHO %USERNAME% started the batch process at %TIME% >output.txt
FOR %%? in ("1.01*.SQL") DO
(
sqlcmd.exe -S local -E -d collect -i %%? >>output.txt
)
pause
The following runs all scripts in a directory, output data to a .log file and errors to a .err.log file:
@echo off
echo "Enter Server"
set /p SERVER=
echo "Enter Database"
set /p DATABASE=
if exist *.log ( del *.log )
rem Process the files
for %%i in (*.sql) do osql -E -S %SERVER% -d %DATABASE% -i"%%i" -m0 -w500 -I -b -r -o"%%i.log" 2>%%i.err.log
rem Remove empty .err files (ie had no errors)
for %%a in (*.err.log) do if %%~za==0 del "%%a"
pause
Note: An earlier version of this used sqlcmd however there proved to be issues with sqlcmd missing out data in the output logs.
To all:
I found some issues with the above, Like Windows 7 Dos did not like the %%i or %%a so I removed an '%' character an then the dos script started to work just fine.
Below is my actual running version of the script where the "1.A00*.SQL" script run truncates my target tables.
The "A*.SQL" run script A01_name.sql --> A24_name.sql sequentially, which is quite nice
Hank Freeman [email protected]
end.
REM " FSA_3.2.2_Runner_for_Insert_Scripts.bat
Rem " Script runner "
Rem " How to use this script"
Rem " From a Remote Desktop session Open a Dos Prompt"
Rem " Run the below script"
Rem " Validate the data has been inserted."
Rem " Hank Freeman July 15th 2014 [email protected]"
Rem " * begin *"
c:
cd \
cd C:\Database_Scripts\Insert_Scripts
@echo off
if exist *.log ( del *.log )
rem "** Starting here *"
rem "run the Truncate SQL file"
for %i in (1.A00_*.SQL) do osql -E -i"%i" -m0 -w500 -I -b -r -o"%i.log" 2>%i.err.log
rem "run the A01-A24 SQL file"
for %i in (A*.SQL) do osql -E -i"%i" -m0 -w500 -I -b -r -o"%i.log" 2>%i.err.log
rem Remove empty .err files (ie had no errors)
for %a in (*.err.log) do if %~za==0 del "%a"
rem "Remove the logs - Manual operation"
rem if exist *.log ( del *.log )
Rem " * end *"
@echo on
