loop that executes all .SQL files in a folder

Question

loop that executes all .SQL files in a folder

manojkanoi

SSCrazy

Points: 2292
More actions
September 1, 2008 at 6:24 pm

#212041

I have lot of queries saved as .sql files in folders and I want to run them in alphabetical order
I found this post which works but the output file does not save the .sql file name that was executed and just saves the result and does not save result if the result was "Command(s) completed successfully.". My main thing is to make sure that all files are run and in alphabetical order. Can you modify this so it always runs in alphabetical order if it is not right now and the output file saves the .sql file names that were executed.
In each folder I put a batch file that contain the following line:
File name : RunScripts.BAT
-----------------------------------------------
for %%X in (*.SQL) do SQLCMD -S ServerName -d DatabasesName -U UserName -P UserPassword -I -i "%%X" >> ResultScript.txt
This batch file uses a for loop that runs the SQLCMD on all of files that match *.SQL that reside in the same folder.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jim Kane-290582 SSC Veteran Points: 279 More actions · Answer 1

Try the following changes to your batch file syntax

for /F %%X in ('dir /b /ON *.sql') do SQLCMD -S ServerName -d DatabasesName -U UserName -P UserPassword -I -i "%%X" > %%X.txt

The /F allows you to use a command instead of just a straight wildcard so (*.sql) becomes ('dir /b /ON *.sql')

basically using the dir command to list the filenames only (/b means bare format) in alphabetical order (/O = ORDER BY N= NAME)

I have also replaced your result file with the parameter so that a separate file is created for each SQL script. This will show you that each SQL script was run.

Cheers,

Jim Kane

manojkanoi SSCrazy Points: 2292 More actions · Answer 2

Thanks for the reply. It looks good but the files do not report the error always. I got one when the object existed. But I tested creating a sp using a wrong column and your version did not show any error but my version showed this error

Msg 107, Level 15, State 1, Server MYSERVER\SQLEXPRESS, Procedure Contact_Customer_S_CustomerList, Line 8

The column prefix 'vCustomersdsdsd_Detail' does not match with a table name or alias name used in the query.

manojkanoi SSCrazy Points: 2292 More actions · Answer 3

I think I found the problem but not a easy fix. Some of my sql files have space in the name and those ones are not throwing any error even if there is one. May be they are not getting executed. It works if I remove the space but they are too many files and i cannot remove space from all of them.

Is there any way you can modify your script to take files with spaces also into consideration.

Jim Kane-290582 SSC Veteran Points: 279 More actions · Answer 4

Yes,

here I have told the FOR command to use forward slash instead of space as a delimiter. This stops it splitting the filename at the space (and / cannot be used in a file name). I have also made sure that I use double quotes around the input and output file names.

Cheers,

Jim

for /F "delims=/" %%X in ('dir /b /ON *.sql') do SQLCMD -S PMP8089\JIM2005 -d master -E -I -i "%%X" > "%%X.txt"

manojkanoi SSCrazy Points: 2292 More actions · Answer 5

Thanks for the reply but now I get no errors even for files without spaces.

Jim Kane-290582 SSC Veteran Points: 279 More actions · Answer 6

How are you executing the batch file? Are you double clicking it or are you opening a command window and executing it manually? I usually find that the latter is better for trouble shooting.

Tremorden Old Hand Points: 344 More actions · Answer 7

It may be printing to the stderr for errors so add a 2>&1 to the end which will redirect stderr to the stdout which is already heading to the output files.

for /F "delims=/" %%X in ('dir /b /ON *.sql') do SQLCMD -S PMP8089\JIM2005 -d master -E -I -i "%%X" > "%%X.txt" 2>&1

Jim Kane-290582 SSC Veteran Points: 279 More actions · Answer 8

Jim Kane-290582

SSC Veteran

Points: 279

September 9, 2008 at 5:06 pm

#869346

An excellent suggestion from Enthusiastic.

Tremorden Old Hand Points: 344 More actions · Answer 9

If I had a nickel for every time I've been called enthusiastic, I'd be rich! 😀

-Tremorden

Jim Kane-290582 SSC Veteran Points: 279 More actions · Answer 10

Oops, sorry Tremorden. I was looking at your level instead of your name. Cheers,

Jim

manojkanoi SSCrazy Points: 2292 More actions · Answer 11

Thanks again guys!

Now I am getting "Sqlcmd: The -E and the -U/-P options are mutually exclusive." for all queries.

manojkanoi SSCrazy Points: 2292 More actions · Answer 12

It works if I remove the -E but I am not sure what it was for. This format works but please confirm if I am doing something wrong

for /F "delims=/" %%X in ('dir /b /ON *.sql') do SQLCMD -S PMP8089\JIM2005 -d master -I -i "%%X" > "%%X.txt" 2>&1

Tremorden Old Hand Points: 344 More actions · Answer 13

Remove the -E (trusted connection) when using -U/-P (username/password).

-Tremorden

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 14

-E does trusted connections (as noted above).

-U/P is for SQL Authentication.

You can only choose one for your connection.