SQL Server Performance

Windows batch file that accepts user input and executes a set of scripts in a folder

Discussion in 'SQL Server 2005 General DBA Questions' started by satya.sqldba, Jan 17, 2010.

  1. satya.sqldba New Member


    I have a set of scripts that need to be executed against ahuge list of sql instances. Instead of opening a connection againsteach sql instance in SSMS and running them one at a time, I am planningto put all of them in a folder and write some kind of batch file toexecute them one after the other. I am trying to write a batch filethat takes user input of the sql instance name and executes the set ofscripts. It also has to accept the input of version if it is 2000 or2005, and execute a different set of scripts based on input. Cansomeone point me in right direction to do this?

  2. MohammedU New Member

    You can use the OSQL instead of batch file to execute the scripts against sql 2000 and 2005.
    You can create a table and insert the sql instance name in it and cursor through the table and execute the scripts saved in folder and output can written to a table or file.
  3. preethi Member

    Alternate to that is SQL 2008 Management studio. You need to group the instances (in registered servers) including 2000 and 2005 in the way you want. Then you can open a query against the group. This is good for quick fixes. I haven't tried the complex scripts on this. However, you can't execute windows commands unless you use xp_cmdshell here.
  4. satya Moderator

  5. satya.sqldba New Member

    Thanks for the replies and suggestions, I was able to do some readingon batch commands and able to come up with below scripts for myrequirements, hope this can help someone in the future. This scripttakes user input of the server name and version number and based on theinput executes the files placed in the folders. My requirement is torun the perf dashboard setup scripts against each instance in ourenterprise and the setup.sql differs from 2005 to 2008. So I had tocome up with the below which takes the servername and stores it in avariable and also the version number and executes the scripts indifferent shares based on the input.

    @echo off
    set /p servername= Please enter the servername:
    set /p version= Please enter the version number 2005 or 2008:
    if %version%==2005 (sqlcmd -E -S %servername% -i "C:atch filesperf_dashboard2005setup.sql" -b
    sqlcmd -E -S %servername% -i "C:atch filesperf_dashboard2005fix.sql" -b)
    if %version%==2008 (sqlcmd -E -S %servername% -i "C:atch filesperf_dashboard2008setup.sql" -b
    sqlcmd -E -S %servername% -i "C:atch filesperf_dashboard2008fix.sql" -b)

  6. satya Moderator

    Excellent, appreciate your knowledge sharing keep it up.

Share This Page