Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

A Little DOS for a Poor DBA Expand / Collapse
Author
Message
Posted Wednesday, November 5, 2008 10:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:06 PM
Points: 33, Visits: 393
Comments posted to this topic are about the item A Little DOS for a Poor DBA
Post #597894
Posted Thursday, November 6, 2008 12:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 5,353, Visits: 1,389
Thanks for bringing back the nostalgia of DOS days. They were very good. By the way good article...


Post #597929
Posted Thursday, November 6, 2008 2:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 1,385, Visits: 1,243
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
Posted Thursday, November 6, 2008 2:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 3:18 AM
Points: 419, Visits: 559
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
Posted Thursday, November 6, 2008 3:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 11, 2013 2:42 AM
Points: 150, 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

Post #597975
Posted Thursday, November 6, 2008 4:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, November 6, 2008 5:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, November 6, 2008 5:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:55 AM
Points: 86, Visits: 478
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
Posted Thursday, November 6, 2008 5:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 22, 2014 5:50 AM
Points: 90, 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
Post #598041
Posted Thursday, November 6, 2008 6:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase 12»»

Permissions Expand / Collapse