Batch file to run a list of .sql files

I've a list of .sql script files to create Stored Procedures. Currently to
create all these Stored Procedures in another machine, I've to open & run
one-by-one from the Query Analzyer. This becomes tedious when the no. grows.
Is there a way to create a batch file that run the scripts, something like
this :

run script1.sql
run script2.sql
run script3.sql
...
run scriptn.sql

Thanks in advance,
Ben

there are 2 ways to do this:
1) use SQL Distributed Management Objects to write an application that reads
the directory and runs scripts form it, for example
2) otherwise, you can create a DOS batch file that calls osql utility which
"allows you to enter Transact-SQL statements, system procedures, and script
files"
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"Ben" <bluebells88@yahoo.com> wrote in message news:3cde4379$1@10.1.10.29...
>
> I've a list of .sql script files to create Stored Procedures. Currently to
> create all these Stored Procedures in another machine, I've to open & run
> one-by-one from the Query Analzyer. This becomes tedious when the no.
grows.
> Is there a way to create a batch file that run the scripts, something like
> this :
>
> run script1.sql
> run script2.sql
> run script3.sql
> ...
> run scriptn.sql
>
> Thanks in advance,
> Ben
>
>

Try using like this in .bat file and then run the batch file

sqlplus -S <user>/<password>@<connect_string> @SQLScripts1.sql

In SQLScripts1.sql If you want add quit at the end so that sqlplus gets disconnected after execution of sql file.

Thanks & Regards,
Ramachandran

Hi Ram

i tried to use the sqlplus cmd for a similar requirement

but am getting the error

the system cannot find the file specified

i have the file in the same path as the batch job and executing it from the same path

can u pls help on this?

regards
Srinandini

Quote:

---

Originally Posted by srinandini

i tried to use the sqlplus cmd for a similar requirement

but am getting the error

the system cannot find the file specified

i have the file in the same path as the batch job and executing it from the same path

---

This may be true, but given that you are getting the error, it is not where your batch job is looking for it.

Which database are you wanting to use - T-SQL is SQL Server, SQLPlus is Oracle?

Am trying to connect to oracle

What do you mean "trying to connect to Oracle"? I'm confused as to exactly what the problem is now. Initially you said

Quote:

---

Originally Posted by srinandini

i tried to use the sqlplus cmd for a similar requirement

but am getting the error

the system cannot find the file specified

i have the file in the same path as the batch job and executing it from the same path

can u pls help on this?

---

This says everything, including any connection things are fine but your batch job is having a problem finding a particular file.

Now you say you are having a problem connecting to Oracle which has nothing whatsoever to do with a batch job finding a file.

What is the problem here?