How to execute multi-file

2 views
Skip to first unread message

Steve

unread,
Feb 27, 2003, 10:48:56 PM2/27/03
to
Hello,

Thanks for Oliviu Burlacu and David Portas in replying me
how to execute multi-SQL.

Now, I would want to know is it possible to execute multi-
file in a batch? or am I need to use osql one by one to
finish my task?

Can anyone suggest me how to perform the same task (see
below) in SQL Server 2000?

Many Thanks!

>-----Original Message-----
>Hi all,
>
>I am new in using SQL Server 2000. In oracle, I can
>execute the SQL file by using "@drive:\RunSQL.sql" in SQL
>Plus. I would want to know what command I should do in
>order to perform the same function? Please help, Thanks!
>
>In RunSQL.sql, the code are like belows:
>------------------------------------------
>DEFINE PATH = 'd:\PLSQL'
>Spool &PATH\Process.log;
>
>Update System_Setting set sys_status = 'OFF',
>Commit;
>
>/* Running Batch */
>@&PATH\anotherSQL1.sql;
>@&PATH\anotherSQL2.sql;
>@&PATH\anotherSQL3.sql;
>
>Update System_Setting set system_status = 'ON',
>Commit;
>
>Spool Off;

Jacco Schalkwijk

unread,
Feb 28, 2003, 7:21:46 AM2/28/03
to
You can create a DOS batch file, and run multiple osql commands from there.
SQL 2000 doesn't really have a batch run utility, osql is still meant to be
used interactively. There are different ways to do things that you might do
with batches. For example you can use jobs to run scheduled batches and
create a self deleting job to run one-off batches.

hth

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


"Steve" <Stev...@hotmail.com> wrote in message
news:091501c2dedc$519efae0$2f01...@phx.gbl...

John Bell

unread,
Feb 28, 2003, 8:52:17 AM2/28/03
to
Hi

You may want to try something like xp_cmdShell and run a batch command

FOR %i IN (*.sql) DO OSQL -n -E -d MYDB -i %i

OR

COPY *.SQL AllSQL.sql
OSQL -n -E -i AllSQL.sql

You could even get the database and server names the T-SQL functions and
pass them along with the required directory.

John

"Steve" <Stev...@hotmail.com> wrote in message
news:091501c2dedc$519efae0$2f01...@phx.gbl...

lindawie

unread,
Feb 28, 2003, 10:28:59 AM2/28/03
to
Steve,

> Now, I would want to know is it possible to execute multi-
> file in a batch? or am I need to use osql one by one to
> finish my task?
>
> Can anyone suggest me how to perform the same task (see
> below) in SQL Server 2000?

You can do this with the osql command line utility using the :r
syntax.

Create a script file that just contains a list of scripts:

:rc:\temp\query1.sql.
:rc:\temp\query2.sql.

There are some restrictions on this. Refer to Books Online.

Or write a little command file that takes a list of all
scripts and executes them one after another in a loop.

@echo off
setlocal

dir /b /on *.sql >tmp.lst

set execsql=osql -SYourServer -dYourDatabase -n -w255 -T

for /F %%i in (tmp.lst) do %execsql% -i%%i

endlocal
goto :eof

Linda

Jacco Schalkwijk

unread,
Feb 28, 2003, 10:43:59 AM2/28/03
to
Linda,

The main restriction seems to be that there is actually no information in
Books Online about the use of :r in script files. Well, not in a place where
I could find it easily at least. I also read something about the possibility
to use variables inside batches. Do you have some more information about
that as well, as that would come in very handy in some installation scripts
I am working on.

Thanks in advance

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


"lindawie" <lind...@my-deja.com> wrote in message
news:uerVU3z3...@TK2MSFTNGP10.phx.gbl...

Reply all
Reply to author
Forward
0 new messages