Running multiple scripts using ForEachLoop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Running multiple scripts using ForEachLoop

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-08-14 : 10:46:10

Folks
I have to run n number of scripts every month.
I can have them with same names example: SQLQuery1.sql and SQLQuery2.sql etc.

When I created the ForEachLoop Container and inside that I have 'Execute SQL Task".

It only runs one script.
It goes to the correct folder though.

Any ideas, I have requested this help from sqlis.com but the moderators have not yet posted this on their forum.

Any timely help is appreciated.
Paresh

Regards
Paresh Motiwala
Boston, USA

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-14 : 10:57:59

1. DOS. Use sqlcmd inside a FORFILES command. This will let you execute any .sql file in a folder.

2. SSIS. In a ForEach Loop, call the Execute Process Task to call sqlcmd.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-08-14 : 11:02:47

Thanks YellowBug
But I would really like to keep it simple by using the Exec SQL Task, not sure how to use the variables to loop through the entire folder and execute one script at a time.

Regards
Paresh Motiwala
Boston, USA

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-14 : 11:11:23

From BOL:
{quote}
The Execute SQL task runs SQL statements or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially. You can use the Execute SQL task for the following purposes:
- Truncate a table or view in preparation for inserting data.
- Create, alter, and drop database objects such as tables and views.
- Re-create fact and dimension tables before loading data into them.
- Run stored procedures.
- Save the rowset returned from a query into a variable.
{/quote}

You cannot use the Exec SQL Task to execute a script file.
Maybe, you can read each file contents into a variable and then use that in the Exec SQL Task variable ???
But there are limitations.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-08-14 : 15:20:34

Hello YellowBug
I tried the following example, it did not work, do you know the exact command?
forfiles -pd:\scripts -v -m*.sql -c"sqlcmd -E -Sservername -dparesh_db -id:\scri
pts\sqlquery1.sql"
but no luck so far, it still runs only the first file....I know I should not be putting the script name there.

Regards
Paresh Motiwala
Boston, USA

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-15 : 06:40:52

Try this, to run every sql script in the folder:
FORFILES -pd:\scripts -m*.sql -c"CMD /C sqlcmd -E -Sservername -dparesh_db -i@FILE"

Or, if it's a fixed list of script files, you can create a .bat file with the sqlcmd command calling each file. Like this:
-----
sqlcmd -E -Sservername -dparesh_db -i"d:\scripts\sqlquery1.sql"
sqlcmd -E -Sservername -dparesh_db -i"d:\scripts\sqlquery2.sql"
sqlcmd -E -Sservername -dparesh_db -i"d:\scripts\sqlquery3.sql"
------

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-08-17 : 10:30:31

sorry buddy, it did not work.
I substituted @file with one file name, and it just ran the same script 5 times.
I hate hardcoding file names and creating a batch job with 20-30 filenames.

Regards
Paresh Motiwala
Boston, USA

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-17 : 10:42:53

What error did you get when you run this:
cd d:\scripts
FORFILES -pd:\scripts -m*.sql -c"CMD /C sqlcmd -E -Sservername -dparesh_db -i@FILE"

Change the servername only.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-08-17 : 11:45:29

This is the error.
Sqlcmd: '@FIle': Invalid filename.

Not sure if I need to declare @FILE as a variable first

Regards
Paresh Motiwala
Boston, USA

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-17 : 12:17:48

No, you don't need to declare the variable.
Can you paste the command your using (copy from the DOS prompt)?
What version of windows are you on?
Are there any spaces in the file names?
Can you see the options when you type forfiles /? at the DOS prompt?

This is the command I ran successfully on Windows Vista:
forfiles /P c:\temp /M *.txt /C "cmd /c sqlcmd -E -S<SERVER> -dmaster -i@FILE"

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-08-17 : 12:59:43

FORFILES -pd:\scripts -m*.sql -c"cmd /c sqlcmd -E -Smyserver -dmydatabase -i@FIle"
my computer has Windows XP PRO.
The file name is sqlquery1.sql and sqlquery2.sql and so on.

The options are not available at the forfiles /? at the DOS prompt.
It lists the files in the folder.

Regards
Paresh Motiwala
Boston, USA

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-17 : 13:20:50

@FILE must be upper-case

Try this:
FORFILES /P d:\scripts /M *.sql /C "cmd /c sqlcmd -E -Smyserver -dmydatabase -i@FILE"

If that doesn't work try this (change the / to -):
FORFILES -P d:\scripts -M *.sql -C "cmd /c sqlcmd -E -Smyserver -dmydatabase -i@FILE"

Or this: (remove the spaces)
FORFILES -Pd:\scripts -M*.sql -C"cmd /c sqlcmd -E -Smyserver -dmydatabase -i@FILE"

Please let me know which one works for Windows XP? If any?

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2009-08-17 : 13:30:10

hmmm
Thanks, it seems the FILE and not File worked.
Now to try it with actual scripts.
Strangely I was able to use the @FILE variable for output too, but with this change @FILE.txt
Here it took the name of the script and appended .txt to the result files.

Thank you YB, this was awesome.

Regards
Paresh Motiwala
Boston, USA

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-17 : 16:30:24

You're welcome. Glad I could help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources