Running a number of .sql files using SQLCMD

Question

I'm trying to put together a batch file to run a large number of .sql files without doing so individually.

So far I have:

@echo off
echo "Enter Server"
set /p SERVER=

echo "Enter Database"
set /p DATABASE=

del *.log

for /r %%i in (*.sql) do sqlcmd -E -S %SERVER% -d %DATABASE% -i"%%i" -m0 -o"%%i.log" -w500 

pause

This works as far as it goes.

However what I really want to do is route any errors to a separate .err file, so that it is very clear at a glance which of the (100+) files failed.

I saw the :Error parameter, but I'm not sure how to apply it here.

Is there an easy way to acheive this?

Answer 1

for /r %%i in (*.sql) do sqlcmd -E -S %SERVER% -d %DATABASE% -i"%%i" -m0 -w500  -b -r  >%%i.log  2>%%i.err

Not perfect since it creates an empty error output file when there are no errors, but a .err file with > 0 bytes will have the errors.

for %%a in (*.err) do if %%~za==0 del "%%a"

..if that's a problem...

Answer 2

You can use sqlsplus (or SQLS*Plus) to execute multiple sql files - check http://www.sqlsplus.com/ - SQLS*Plus is like an Oracle© SQL*Plus / sqlplus SQL command line tool for Microsoft© SQLServer 2008, 2005 and 2000

1. execute file like:

@1.sql @2.sql @3.sql

1. Spool errors:

spool 1.log ... spool off

spool 2.log ... spool off