SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Little DOS for a Poor DBA


A Little DOS for a Poor DBA

Author
Message
Yakov Shlafman
Yakov Shlafman
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 516
Comments posted to this topic are about the item A Little DOS for a Poor DBA
Anipaul
Anipaul
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12625 Visits: 1407
Thanks for bringing back the nostalgia of DOS days. They were very good. By the way good article...



Tao Klerks
Tao Klerks
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2431 Visits: 1249
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 Smile

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
mosaic-263591
Mr or Mrs. 500
Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)

Group: General Forum Members
Points: 539 Visits: 566
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:
[url=http://www.microsoft.com/windowsserver2003/technologies/management/powershell/default.mspx][/url]
mike brockington
mike brockington
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 245
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
dbaker-620086
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1144 Visits: 196
"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
alan.bourke
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 7
Thank you for a very informative article.

Tongueedantic 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.

Tongueedantic Mode OFF:
Pieter-423357
Pieter-423357
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 Visits: 577
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
jims-723592
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 103
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
ron.bracale
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 20
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.

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 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search