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 2 of 2

««1 2

A Little DOS for a Poor DBA

Rate Topic

Display Mode

Topic Options

Author

Message

Robert Livermore

Posted Thursday, November 06, 2008 6:35 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:20 AM
Points: 196, Visits: 49

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

Post #598069

Jimbo-712809

Posted Thursday, November 06, 2008 6:36 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 18, 2011 7:35 AM
Points: 19, Visits: 69

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.

Post #598070

Larry Aue

Posted Thursday, November 06, 2008 6:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 142, Visits: 524

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.

Post #598074

dchamberlin

Posted Thursday, November 06, 2008 6:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 6:41 AM
Points: 8, Visits: 20

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

Post #598076

noeld

Posted Thursday, November 06, 2008 7:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977

Good old days... Thanks for the reminiscing!

* Noel

Post #598162

Charles Kincaid

Posted Thursday, November 06, 2008 8:04 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825

Try this:

@echo off
if #%1# == ## goto noparm
echo running %1
echo ####################### running %1 #######################>>log.txt
osql -U  log.txt
goto :done
:noparm
echo ####################### Start Process #######################>log.txt
for %%X in (*.sql) do (call %0 %%X)
echo ####################### End Process #######################>>log.txt
start notepad log.txt
:done

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.

ATB

Charles Kincaid

Post #598173

Fidel Rodriguez

Posted Thursday, November 06, 2008 12:27 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, August 16, 2012 1:55 PM
Points: 11, Visits: 188

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.txt
The 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.txt
for /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.log
del blahtemp12345.txt
____________________________________________________

Sample my_server_list.txt
______________________________
ServerA
ServerB
ServerC
...
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.sql
Runonce 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 perspect

runonce.bat code
__________________________________________________
@@echo off
@rem Run a specified sql script file against a list of PCs
@rem %1 - list of servers
@rem %2 - sql script

for /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

Post #598457

« Prev Topic | Next Topic »

17 posts, Page 2 of 2

««1 2

Permissions