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 Thursday, November 6, 2008 6:35 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, November 20, 2014 11:56 AM
Points: 197, Visits: 74
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
Posted Thursday, November 6, 2008 6:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, November 6, 2008 6:42 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:14 PM
Points: 148, Visits: 655
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
Posted Thursday, November 6, 2008 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, November 6, 2008 7:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
Good old days... Thanks for the reminiscing!


* Noel
Post #598162
Posted Thursday, November 6, 2008 8:04 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:41 AM
Points: 809, Visits: 1,996
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

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
Posted Thursday, November 6, 2008 12:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 5:18 PM
Points: 11, 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
Post #598457
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse