A Little DOS for a Poor DBA

  • Yakov Shlafman

    SSCommitted

    Points: 1613

    Comments posted to this topic are about the item A Little DOS for a Poor DBA

  • Anipaul

    SSC-Insane

    Points: 24681

    Thanks for bringing back the nostalgia of DOS days. They were very good. By the way good article...

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4297

    Not to knock batch file programming (there's some really impressive stuff you can do, lots of enthusiasts out there), but if this is a windows environment wouldn't VBScript (run using CScript.exe, from the command-line) be acceptable? This gives you nice error-handling, backwards compatibility to at least w2k & SQL2K, very easy access to any com objects (DMO or SMO), etc.

    Of course, that's without going into the new fancy powershell stuff 🙂

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • mosaic-263591

    Right there with Babe

    Points: 727

    Just looking at the painful DOS syntax such as this fragment

    "%CRT_DIR%\%SCR_NAME%%SCR_EXT%"

    makes me wince. I would suggest using PowerShell and perhaps a CSV file to feed "environment" variables to the script.

    Powershell is a command line shell and scripting language and runs on XP, Vista and Server 2003.

    It allows you to use the good old DOS commands (if you really want to), WMI, Active Directory, COM objects, the full .NET functionality and more - all on the same command line.

    Like UNIX it uses the concept of a pipeline, but it is a pipeline of objects (not text), which avoids the need to parse the data between commands.

    I'm still learning but I think it is well worth the investment. Check it out here:

  • Mike Brockington

    SSC Eights!

    Points: 874

    Yes, very 'Powerful' stuff indeed.

    Round here, 'Powerful' is always regarded as a synonym for 'Dangerous' and I don't think this article disproves that point!

    Throw away your pocket calculators; visit www.calcResult.com
  • dbaker-620086

    SSCrazy

    Points: 2336

    "Enterprise Manager" and "OSQL" in 2008, when SQL2005 brought in SSMS and SQLCMD ??

    on a more worrying note the author makes no allowance for errors

    - network, deadlocking, resource, shutdowns etc happen in real-world, so prepare for it !

    and having a series of OSQL or SQMCND lines is dangerous (failure of step 2 will fall into steps 3..n)

    for example on SQLCMD you should use "-b -m 10" arguments (see BOL for details)

    - if any error happened during the execution, would return a non-zero code to CMD/COMMAND

    thus after each command-line run you should have a line

    IF ERRORLEVEL 1 goto BOMB

    where :BOMB label does some recovery/cleanup/advice as appropriate

    hence the benefits of structured exception handling (TRY..CATCH) and similar powerful constructs in PowerShell with cleaner code than the screeds of ugly IF .. GOTO statements

    MORAL: errors happen, so don't be a victim of bad planning. keep your shop running and you job

    enjoy!

    Dick

  • alan.bourke

    SSC Enthusiast

    Points: 114

    Thank you for a very informative article.

    :Pedantic Mode ON:

    CMD.EXE is not DOS. It's a 32-bit command line that supports DOS commands. COMMAND.COM is DOS, and is 16-bit.

    :Pedantic Mode OFF:

  • Pieter-423357

    SSCommitted

    Points: 1619

    DOS, Scripts, and SQLCMD; it still makes the world go around.

    More beneficial is that wWhen troubleshooting occurs, this setup is readable every step of the way. I found this difficult in some of the alternatives.

  • jims-723592

    SSC Veteran

    Points: 202

    Next step would be, in non-destructive scripting, foreach

    e.g.

    (Using 2 batch files)

    In first, pick which files to run:

    @Set Svr=ServerToRunAgainst

    @Rem For Non-Integrated @Set lgn=-U sa -P password

    @Set lgn=-E

    @Set Db=-d SOMEDB

    @for /R "..\Create Scripts" %%f in (*.Sql) do Call ProcessOneFile.Bat "%%f"

    And in second, the ProcessOneFile:

    @SET passedInFile=%1

    @SET passedInFile=%passedInFile:"=%

    @Set TmpFileFull=C:\Temp\Tmp.Sql

    @copy "%passedInFile%" "%TmpFileFull%"

    @Echo Processing %passedInFile%

    @SqlCmd -S %Svr% %lgn% %Db% -i "%TmpFileFull%"

    To deploy, I run the batch file. It will then run every SQL file in the subfolder "Create Scripts"

    Copy to the Tmp was to work around the long name issues

    Can, of course, modify the SqlCmd, for me it's an environment variable with a FULL path to oSql

  • ron.bracale

    SSC Enthusiast

    Points: 132

    DOS is very useful. I have an application that uses SQL2000. I often create CSV files in DTS to FTP to Hosted applications.

    I use a BAT file to run an FTP script and also to datestamp an archived version. The date is using the date string, so the date format on the server is critical and the offsets might be different.

    http://FTP.BAT

    set today=%DATE%

    set mm=%today:~0,2%

    set dd=%today:~3,2%

    set yyyy=%today:~6,4%

    set ts=%yyyy%%mm%%dd%

    ftp -s:ftpput.ftp > ftpput_%ts%.log

    copy /B /Y \\Srvr\dir\Dir\My.csv \\Srvr\dir\Dir\Archive\%ts%_My.csv

    FTPPUT.FTP

    open http://ftp.Hosted.com

    username

    password

    cd xyz

    lcd \Dir\Dir

    binary

    put My.csv

    bye

    It is easy and the FTP itself is also piped to a log.

  • Robert Livermore

    SSC-Addicted

    Points: 403

    Deployments need 2 interpreters. One for hosting the deployment, logging and error detection the other is TSQL script client.

    1) The DOS command interpreter is gone. You are really using the NT Command Interpreter. There are differences. I have tried several interpreters for deployments, CScript (Have used this for years. Better error handling and custom logging than NT Command Interpreter) More recently using powershell 1.0. (Very Very good interpreter. Best feature is structured error handling, parameters as object. WOW)

    2) OSQL and ISQL are depreciating commands. Use SQLCMD instead. Hot tip. Take alook at the parameterized scripts with SQLCMD. Useful for TSQL literals you do not know until deployment time Example database names, dynamic Table Names etc. Use your interpreter (powershell) to pass in parameters to TSQL scripts in the hosted in SQLCMD.

    Rob

  • Jimbo-712809

    SSC Enthusiast

    Points: 197

    Oh, man, I have to laugh at some of these responses. It's very easy to see who the uber-geeks are, and who has spent only a handful of years in the industry.

    When I started, Unix SVR4 was da bomb, and the best 3GL language out there was plain old C. Because these were the first things I used on the job, I eventually became proficient and totally biased. Now I work solely with Windows OS products and tools, but the Unix/C days taught me very many valuable lessons.

    The most important -- and this was summarized at the end of the article -- was the value of using what you already have. Yes, if you have PowerShell and you know how to use it well, then by all means kick some butt. In 20 years, it will replace DOS as the old piece-of-crap brain-dead tool the noobs like to make fun of.

    On another note, I don't think anyone mentioned any Javascript-esque tools. I tend to use JScript a lot when scripting automation tools because I am so familiar with the syntax and concepts.

    And, I use TextPad.

    P.S. I just saw that someone mentioned Cscript, which is the correct tool name. JScript is just the language one uses.

  • Larry Aue

    Ten Centuries

    Points: 1027

    One thing to note with DOS commands (if you're running them via SQL Server Agent) is that you are limited to the number of command shell threads you can run at once, set via sp_configure 'max worker threads' in SQL 2000, at least. I've not tried it in 2005.

  • dchamberlin

    SSC Rookie

    Points: 30

    Excellent Article - DOS (command prompt commands) still have a lot of value in task automation.

    A LOT of really good techniques demonstrated here.

  • noeld

    SSC Guru

    Points: 96590

    Good old days... Thanks for the reminiscing!


    * Noel

Viewing 15 posts - 1 through 15 (of 17 total)

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