A Little DOS for a Poor DBA

• Charles Kincaid

  SSChampion

  Points: 13593

  More actions

  November 6, 2008 at 8:04 am

  #894902

  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.

  ATBCharles Kincaid

• Fidel Rodriguez

  SSC Enthusiast

  Points: 145

  More actions

  November 6, 2008 at 12:27 pm

  #895148

  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