November 5, 2008 at 10:12 pm
Comments posted to this topic are about the item A Little DOS for a Poor DBA
November 6, 2008 at 12:59 am
Thanks for bringing back the nostalgia of DOS days. They were very good. By the way good article...
November 6, 2008 at 2:11 am
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.
November 6, 2008 at 2:38 am
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:
November 6, 2008 at 3:27 am
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!
November 6, 2008 at 4:06 am
"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
November 6, 2008 at 5:07 am
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:
November 6, 2008 at 5:46 am
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.
November 6, 2008 at 5:58 am
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
November 6, 2008 at 6:17 am
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.
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
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.
November 6, 2008 at 6:35 am
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
November 6, 2008 at 6:36 am
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.
November 6, 2008 at 6:42 am
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.
November 6, 2008 at 6:43 am
Excellent Article - DOS (command prompt commands) still have a lot of value in task automation.
A LOT of really good techniques demonstrated here.
November 6, 2008 at 7:59 am
Good old days... Thanks for the reminiscing!
* Noel
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply