Windows Batch-fu for Microsoft SQL Server 2005
Assuming that %1 is set to a valid path and %2 the name of a database, and the script is being run from the database server itself:
echo Testing %1 to ensure that it is a directory... if exist %1\NUL (echo It is.) else goto forced_exit for %%i in (%1\*.sql) do sqlcmd -E -d %2 -m-1 -p 1 -o %%i.log -i %%i if /I %ERRORLEVEL% gtr 0 goto forced_exit goto exit :forced_exit echo Exit forced. Something went wrong. exit /b 99 :exit
Will run any SQL command files suffixed .sql in the specified directory, in the directory sort order. If you’ve named your scripts: 01_dosomething.sql, 02_dosomethingelse.sql then you’ve got yourself the beginings of a very crude deployment system. There will be a .log file generated for each script that is run.
The parameters to sqlcmd are tweakable if you need remote connections. You might want to also tweak the options that dictate what SQL error level sqlcmd traps and passes on as its %ERRORLEVEL% if you really really need to stop on the first failure.
