SQLServerCentral / Discuss Content Posted by Yakov Shlafman / Article Discussions / Article Discussions by Author / A Little DOS for a Poor DBA / Latest Posts

RE: A Little DOS for a Poor DBA

Fidel Rodriguez — Thu, 06 Nov 2008 12:27:32 GMT

Hey All, Here's are some scripts that I've used a couple of times in our environment. They work well if a script or many scripts have to be run against a bunch of servers. I've added some of the sqlcmd parameters to allow for proper formatting in the log files. First batch file:Example:Assume that the file is called "runall.bat" and it's placed in c:\utils and the command is executed as follows: runall.bat my_server_list.txtThe script will assume that all of the files placed in c:\utils\sql_scripts are scripts and will run them against all of the servers listed in my_server_list.txt.Code for runall.bat___________________________________________________dir /b sql_scripts > blahtemp12345.txtfor /F "eol=; tokens=1" %%h in (blahtemp12345.txt) do for /F "eol=; tokens=1" %%i in (%1) do sqlcmd -s "|" -S %%i -E -W -h -1 -i .\sql_scripts\%%h > .\logs\%%i-%%h.logdel blahtemp12345.txt____________________________________________________Sample my_server_list.txt______________________________ServerAServerBServerC...ServerN______________________________Second batch file code (used for one-off script execution)Assume the file is called runonce.bat and is placed in c:\utils and the command is invoked as follows:runonce.bat my_server_list.txt my_sql_script.sqlRunonce will iterate through all of the servers in my_server_list.txt and execute the sql script my_sql_script.sql against them. All logs will be outputted to .\logs from the perspectrunonce.bat code__________________________________________________@@echo off@rem Run a specified sql script file against a list of PCs @rem %1 - list of servers@rem %2 - sql scriptfor /F "eol=; tokens=1" %%i in (%1) do sqlcmd -s "|" -S %%i -E -i %2 > logs\%%i-%2.log___________________________________________________Feedback is always appreciated - thanks!---Fidel

RE: A Little DOS for a Poor DBA

Charles Kincaid — Thu, 06 Nov 2008 08:04:33 GMT

Try this:[code]@echo offif #%1# == ## goto noparmecho running %1echo ####################### running %1 #######################>>log.txtosql -U log.txtgoto :done:noparmecho ####################### Start Process #######################>log.txtfor %%X in (*.sql) do (call %0 %%X)echo ####################### End Process #######################>>log.txtstart notepad log.txt:done[/code]Put all your script files into the same directory. How do you control the order they run in? Simple. By default NTFS directory results appear in alphabetic order :cool:I wrote a Dot Net program that dumps tables as insert statements. I run that to export data and run this to do the imports. This way i avoid all the hassle of column order issues with BCP.

RE: A Little DOS for a Poor DBA

noeld — Thu, 06 Nov 2008 07:59:56 GMT

Good old days... Thanks for the reminiscing!

RE: A Little DOS for a Poor DBA

dchamberlin — Thu, 06 Nov 2008 06:43:58 GMT

Excellent Article - DOS (command prompt commands) still have a lot of value in task automation.A LOT of really good techniques demonstrated here.

RE: A Little DOS for a Poor DBA

Larry Aue — Thu, 06 Nov 2008 06:42:42 GMT

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.

RE: A Little DOS for a Poor DBA

Jimbo-712809 — Thu, 06 Nov 2008 06:36:05 GMT

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.

RE: A Little DOS for a Poor DBA

Robert Livermore — Thu, 06 Nov 2008 06:35:37 GMT

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

RE: A Little DOS for a Poor DBA

ron.bracale — Thu, 06 Nov 2008 06:17:02 GMT

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.BATset 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%.logcopy /B /Y \\Srvr\dir\Dir\My.csv \\Srvr\dir\Dir\Archive\%ts%_My.csvFTPPUT.FTPopen ftp.Hosted.comusernamepasswordcd xyzlcd \Dir\Dirbinaryput My.csvbyeIt is easy and the FTP itself is also piped to a log.

RE: A Little DOS for a Poor DBA

jims-723592 — Thu, 06 Nov 2008 05:58:44 GMT

Next step would be, in non-destructive scripting, foreache.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 issuesCan, of course, modify the SqlCmd, for me it's an environment variable with a FULL path to oSql

RE: A Little DOS for a Poor DBA

Pieter-423357 — Thu, 06 Nov 2008 05:46:52 GMT

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.

RE: A Little DOS for a Poor DBA

alan.bourke — Thu, 06 Nov 2008 05:07:29 GMT

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:

RE: A Little DOS for a Poor DBA

dbaker-620086 — Thu, 06 Nov 2008 04:06:20 GMT

"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/COMMANDthus after each command-line run you should have a line IF ERRORLEVEL 1 goto BOMBwhere :BOMB label does some recovery/cleanup/advice as appropriatehence the benefits of structured exception handling (TRY..CATCH) and similar powerful constructs in PowerShell with cleaner code than the screeds of ugly IF .. GOTO statementsMORAL: errors happen, so don't be a victim of bad planning. keep your shop running and you job enjoy!Dick

RE: A Little DOS for a Poor DBA

mike brockington — Thu, 06 Nov 2008 03:27:24 GMT

Yes, very 'Powerful' stuff indeed.Round here, 'Powerful' is always regarded as a synonym for 'Dangerous' and I don't think this article disproves [i]that [/i]point!

RE: A Little DOS for a Poor DBA

mosaic-263591 — Thu, 06 Nov 2008 02:38:28 GMT

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]

RE: A Little DOS for a Poor DBA

Tao Klerks — Thu, 06 Nov 2008 02:11:12 GMT

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 :)

RE: A Little DOS for a Poor DBA

Anipaul — Thu, 06 Nov 2008 00:59:56 GMT

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

A Little DOS for a Poor DBA

Yakov Shlafman — Wed, 05 Nov 2008 22:12:57 GMT

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