isql -- executing multiple input scripts

[Alex Vilner]

Hello!

We have a set of individual .SQL scripts which we would like to
execute against a MS SQL Server 2000. Is there any way to have ISQL
utility (or any other means) to execute all of them without having to
establish a separate database connection for each script:

isql -Ux -Py -Ss -i script1.sql
isql -Ux -Py -Ss -i script2.sql
isql -Ux -Py -Ss -i script3.sql
.....
isql -Ux -Py -Ss -i scriptN.sql

All scripts are in the same location, which could be made visible to
by the SQL Server itself (so either a client-based or a server-based
invokation is ok)

TIA

[Simon Hayes]

al**@sinoma.com (Alex Vilner) wrote in message news:<22**************************@posting.google. com>...

Hello!

We have a set of individual .SQL scripts which we would like to
execute against a MS SQL Server 2000. Is there any way to have ISQL
utility (or any other means) to execute all of them without having to
establish a separate database connection for each script:

isql -Ux -Py -Ss -i script1.sql
isql -Ux -Py -Ss -i script2.sql
isql -Ux -Py -Ss -i script3.sql
....
isql -Ux -Py -Ss -i scriptN.sql

All scripts are in the same location, which could be made visible to
by the SQL Server itself (so either a client-based or a server-based
invokation is ok)

TIA

A minimum solution would be a batch command (untested):

for %f in (*.sql) do isql -Ux -Py -Ss -i %f

But if you write a wrapper script in a language like Perl or VBScript
you can have better control over the order the scripts are executed in
etc.

Simon

[Alex Vilner]

Simon, thank you for the suggestion.
This would work if scripts were independent of each other. However,
they need to be executed in the specific order, so the FOR loop will
not do it.

Oracle has a nice and easy way:
connect in SQLPlus, and then just do this:

@script1.sql
@script2.sql
@script3.sql
...
@scriptN.sql

All within the same session. Can MS SQL Server do that is the question
:)

sq*@hayes.ch (Simon Hayes) wrote in message news:<60**************************@posting.google. com>...

al**@sinoma.com (Alex Vilner) wrote in message news:<22**************************@posting.google. com>...

Hello!

We have a set of individual .SQL scripts which we would like to
execute against a MS SQL Server 2000. Is there any way to have ISQL
utility (or any other means) to execute all of them without having to
establish a separate database connection for each script:

isql -Ux -Py -Ss -i script1.sql
isql -Ux -Py -Ss -i script2.sql
isql -Ux -Py -Ss -i script3.sql
....
isql -Ux -Py -Ss -i scriptN.sql

All scripts are in the same location, which could be made visible to
by the SQL Server itself (so either a client-based or a server-based
invokation is ok)

TIA

A minimum solution would be a batch command (untested):

for %f in (*.sql) do isql -Ux -Py -Ss -i %f

But if you write a wrapper script in a language like Perl or VBScript
you can have better control over the order the scripts are executed in
etc.

Simon

[Simon Hayes]

"Alex Vilner" <al**@sinoma.com> wrote in message
news:22**************************@posting.google.c om...

Simon, thank you for the suggestion.
This would work if scripts were independent of each other. However,
they need to be executed in the specific order, so the FOR loop will
not do it.

Oracle has a nice and easy way:
connect in SQLPlus, and then just do this:

@script1.sql
@script2.sql
@script3.sql
...
@scriptN.sql

All within the same session. Can MS SQL Server do that is the question
:)

Yes, if you create a wrapper file which lists each script like this:

:r c:\scripts\script1.sql
:r c:\scripts\script2.sql
:r c:\scripts\script3.sql

Then execute it like this:

isql -S MyServer -d MyDatabase -E -i c:\scripts\wrapper.sql

That will load and execute each file in turn. You can also load the files
interactively, according to the documentation, although I personally only
use osql in batches, so I haven't tried it.

Simon

[Alex Vilner]

Have an issue with this:
1> 80> 159> 361> 415> 447> Msg 170, Level 15, State 1, Server DEVSQL01, Line 4
Line 4: Incorrect syntax near 'GO'.
Msg 170, Level 15, State 1, Server DEVSQL01, Line 7
Line 7: Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Server DEVSQL01, Line 14
'CREATE PROCEDURE' must be the first statement in a query batch.
......

The script for procedure creation has tis general structure:
/* description */
if exists ...
drop procedure
go

SET ...
SET ...

/* comment */
create procedure ...
go

It does not seem to like the GOs inside the scripts... This is using isql.

Any other hints/suggestions?

Thanks!
"Simon Hayes" <sq*@hayes.ch> wrote in message news:<3f********@news.bluewin.ch>...

"Alex Vilner" <al**@sinoma.com> wrote in message
news:22**************************@posting.google.c om...

Simon, thank you for the suggestion.
This would work if scripts were independent of each other. However,
they need to be executed in the specific order, so the FOR loop will
not do it.

Oracle has a nice and easy way:
connect in SQLPlus, and then just do this:

@script1.sql
@script2.sql
@script3.sql
...
@scriptN.sql

All within the same session. Can MS SQL Server do that is the question
:)

Yes, if you create a wrapper file which lists each script like this:

:r c:\scripts\script1.sql
:r c:\scripts\script2.sql
:r c:\scripts\script3.sql

Then execute it like this:

isql -S MyServer -d MyDatabase -E -i c:\scripts\wrapper.sql

That will load and execute each file in turn. You can also load the files
interactively, according to the documentation, although I personally only
use osql in batches, so I haven't tried it.

Simon

[Erland Sommarskog]

Alex Vilner (al**@sinoma.com) writes:

Have an issue with this:
1> 80> 159> 361> 415> 447> Msg 170, Level 15, State 1, Server DEVSQL01,
Line 4
If you use the -n options, you will not have to look at those
80> etc.
Line 4: Incorrect syntax near 'GO'.
Msg 170, Level 15, State 1, Server DEVSQL01, Line 7
Line 7: Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Server DEVSQL01, Line 14
'CREATE PROCEDURE' must be the first statement in a query batch.
.....

It's a bit difficult to tell what is going on, since you only posted an
outline of your script. It may help if you can post a script which
exhibits the problem.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Simon Hayes]

al**@sinoma.com (Alex Vilner) wrote in message news:<22*************************@posting.google.c om>...

Have an issue with this:
1> 80> 159> 361> 415> 447> Msg 170, Level 15, State 1, Server DEVSQL01, Line 4
Line 4: Incorrect syntax near 'GO'.
Msg 170, Level 15, State 1, Server DEVSQL01, Line 7
Line 7: Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Server DEVSQL01, Line 14
'CREATE PROCEDURE' must be the first statement in a query batch.
.....

The script for procedure creation has tis general structure:
/* description */
if exists ...
drop procedure
go

SET ...
SET ...

/* comment */
create procedure ...
go

It does not seem to like the GOs inside the scripts... This is using isql.

Any other hints/suggestions?

Thanks!

Without seeing the whole script, it's not clear why you have
"incorrect syntax" errors, however the third error is clear - CREATE
PROC must be the first statement in a batch. You have two SET
statements, then CREATE PROC. If you use Enterprise Manager or Query
Analyzer to generate the CREATE PROC script, you'll see how it
separates these statements into separate batches:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists ...
GO
CREATE procedure dbo.SomeProc
....
GO
Simon