SQLCMD and Batch File magic

  • Thanks for the article.

    I'm sure .bat files are still used a lot and it would take years to phase them out.

    I would also prefer a Powershell alternative.

  • Haven't used this sort of thing in a while.

  • 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

  • wouldnt call it Error. They're Outputs. Might not be errors. Ie. rowcounts. etc.

  • 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

    Nice.

  • schleep - Thursday, November 19, 2015 6:03 AM

    Try this in Powershell: this basic functionality is relatively simple to learn.You'll never go back to .bat

    I know it's an old post but considering what the FORFILES command can do at the command line, I never left .bat. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • As a bit of a sidebar, I'd never use a user name and password to connect to the server because they leave your stuff in a batch file in clear text.  Always use "trusted connections" and Windows Authentication.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • @hiramfleitas: Error is the correct description because if "Msg " appears in the output it's an error 😉
    @Jeff Moden: You are right. This is 100% my oppinion. I've some additional lines how this can be solved. Take Username and Pass as parameter:

    ...
    set Errormessages=%LogPath%\ErrorMessages.txt

    rem Optional use Windows or SQL-authentication
    set Logon=-E
    if .%4.==.. goto StartWorking
    set Logon=-U %3 -P %4
    goto StartWorking

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

    Then replace the SQLCMD call with this:

    sqlcmd -S %SQLServer% -d "%DB%" -b %Logon% -i "%ScriptPath%\%%A" -o "%LogPath%\%%A_%DB%_Errorlog.txt" -I

    and add the following Help text...

    echo 4.) Optional: SQL-Username and Password

Viewing 8 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply