roland.hangg - Friday, July 28, 2017 3:59 AM

Hi Manu, I saw your short script in the newsletter today and it inspired me. Thanks! Yes powershell is nice but you always need the correct environment, powershell version, permissions, ... to much requirements for my taste. CMD runs everywhere for emergency also on WINXP/2003 machines. So I've developed your script to a pretty little tool. This runs with UNC pathes and also blanks in pathes, file names and database names. It aborts if one SQL script throws an error. Just call it with three parameters:
1.) Location of your SQL scripts e.g. C:\MyScripts
2.) SQL-Server and port, e.g. "MySQLServer,1433"
3.) Database name, e.g. AdventureWorks
The batch connects with integrated security but this part of script is easy to change if you want to connect with SQL-Server authentication. Feel free to save the code into a file called e.g. SQLBatch.cmd and try it...

@rem (c)2017 Roland Hangg
@rem Many thanks to Manu Mohanan for the inspiration
@rem Version 20170728
@echo off
if .%3.==.. goto help
rem set variables
set ScriptPath=%1
set ScriptPath=%ScriptPath:"=%
set LogPath=%ScriptPath%\Log
set SQLServer=%2
set DB=%3
set DB=%DB:"=%
set ErrorString="Msg "
set Errormessages=%LogPath%\ErrorMessages.txt

rem remove old logfiles
if exist "%LogPath%\*.*" del "%LogPath%\*.*" /f /s /q >nul
if not exist "%LogPath%\*.*" md "%LogPath%"

rem run scripts in script path. Abort if an error occurs
FOR /F "delims=" %%A IN ('dir /b /on "%ScriptPath%\*.SQL"') DO (
    set RunningScript=%%A
    echo Running %%A...
    sqlcmd -S %SQLServer% -d "%DB%" -b -E -i "%ScriptPath%\%%A" -o "%LogPath%\%%A_%DB%_Errorlog.txt" -I
    echo findstr /L /C:%ErrorString% "%LogPath%\%%A_%DB%_Errorlog.txt" >"%Errormessages%"
    findstr /L /C:%ErrorString% "%LogPath%\%%A_%DB%_Errorlog.txt" >"%Errormessages%"
    if not errorlevel 1 goto errorinscript
    )
goto end

:errorinscript
echo Got a problem running script %RunningScript%!
echo Press any key to see errors or CTRL+C to exit...
pause >nul
rem type "%LogPath%\%RunningScript%_%DB%_Errorlog.txt"
type "%Errormessages%"
echo Abording...
goto end

:help
cls
echo Please provide parameters:
echo 1.) Location of your SQL scripts e.g. C:\MyScripts
echo 2.) SQL-Server and port, e.g. "MySQLServer,1433"
echo 3.) Database name, e.g. AdventureWorks
goto end

:end

Best regards, Roland