SQLCMD and Batch File magic

Question

Post reply

SQLCMD and Batch File magic

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

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

Stefan LG SSCommitted Points: 1936 More actions · Answer 1

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.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

November 23, 2015 at 1:57 pm

#1841851

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

roland.hangg Old Hand Points: 321 More actions · Answer 3

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

hiramfleitas Valued Member Points: 64 More actions · Answer 4

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

Thanks,
Hiram
http://fleitasarts.com

Manu Mohanan SSC Journeyman Points: 99 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

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)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

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)

roland.hangg Old Hand Points: 321 More actions · Answer 8

@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