Sadequl Hussain (7/30/2009)
Great discussion... !!! Folks, I really appreciate all the feedback and the ideas... this is how you learn when you write and share 🙂One thing I am not sure - and correct me if I am wrong - is if the batch file shown here creates one seprate output text file for each of the scripts executed. Ideally, each of the source script output should be in a seperate text output file.
That's easy! If your batch file is called runit.bat and contains
for %%f in (%2\*.sql) do SQLCMD -E -S "(local)" -i %%f -o %2\output\%%~nf.out -b -d %1 || exit /b
You would run the batch file as
C:\temp\sqlscripts\runit.bat adventureworks C:\temp\sqlscripts
All files in C:\temp\sqlscripts with the .sql extention will be executed with output files in the C:\temp\sqlscripts\output sub-directory using the body of the file name and a .out extension e.g C:\temp\sqlscripts\1_Firstfile.sql will have C:\temp\sqlscripts\output\1_Firstfile.out. If you have a script that fails e.g 2_Secondfile.sql containing the command RAISERROR ( 'Error', 16 , 1 ), then all that will be run is 1_Firstfile.sql and 2_Secondfile.sql and C:\temp\sqlscripts\output\2_Secondfile.out would contain:
Msg 50000, Level 16, State 1, Server MyPC, Line 1
Error
So the last file created is where the error occured.
Martin