A Little DOS for a Poor DBA

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Yakov Shlafman » A Little DOS for a Poor DBA

17 posts, Page 1 of 2

1 2 »»

A Little DOS for a Poor DBA

Rate Topic

Display Mode

Topic Options

Author

Message

Yakov Shlafman

Posted Wednesday, November 05, 2008 10:12 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 6:20 AM
Points: 33, Visits: 324

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

Post #597894

Anipaul

Posted Thursday, November 06, 2008 12:59 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 1:35 AM
Points: 4,789, Visits: 1,336

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

Post #597929

Tao Klerks

Posted Thursday, November 06, 2008 2:11 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, April 14, 2013 8:55 AM
Points: 1,383, Visits: 1,212

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.

Post #597947

mosaic-263591

Posted Thursday, November 06, 2008 2:38 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 419, Visits: 556

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]

Post #597958

mike brockington

Posted Thursday, November 06, 2008 3:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:54 AM
Points: 119, Visits: 214

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

Post #597975

dbaker-620086

Posted Thursday, November 06, 2008 4:06 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 28, 2009 2:09 AM
Points: 112, 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

Post #597989

alan.bourke

Posted Thursday, November 06, 2008 5:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 15, 2013 7:09 AM
Points: 2, Visits: 7

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:

Post #598016

Pieter-423357

Posted Thursday, November 06, 2008 5:46 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 1:26 PM
Points: 86, Visits: 473

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.

Post #598031

jims-723592

Posted Thursday, November 06, 2008 5:58 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 7:35 AM
Points: 90, Visits: 102

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

Post #598041

ron.bracale

Posted Thursday, November 06, 2008 6:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 24, 2012 6:07 AM
Points: 2, 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.

Post #598057

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions