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
Robert Livermore
Robert Livermore
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 81
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



Jimbo-712809
Jimbo-712809
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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.
Larry Aue
Larry Aue
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

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



dchamberlin
dchamberlin
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 21
Excellent Article - DOS (command prompt commands) still have a lot of value in task automation.
A LOT of really good techniques demonstrated here.
noeld
noeld
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: 12726 Visits: 2048
Good old days... Thanks for the reminiscing!


* Noel
Charles Kincaid
Charles Kincaid
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

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

ATBCharles Kincaid
Fidel Rodriguez
Fidel Rodriguez
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 192
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
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