isql -- executing multiple input scripts

**Simon Hayes** · Jul 20 '05, 02:10 AM

Re: isql -- executing multiple input scripts

alex@sinoma.com (Alex Vilner) wrote in message news:<22e9f6e0. 0311251339.24cc f4a5@posting.go ogle.com>...[color=blue]
> 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[/color]

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** · Jul 20 '05, 02:11 AM

Re: isql -- executing multiple input scripts

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
:)

sql@hayes.ch (Simon Hayes) wrote in message news:<60cd0137. 0311260057.6f81 9165@posting.go ogle.com>...[color=blue]
> alex@sinoma.com (Alex Vilner) wrote in message news:<22e9f6e0. 0311251339.24cc f4a5@posting.go ogle.com>...[color=green]
> > 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[/color]
>
> 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[/color]

**Simon Hayes** · Jul 20 '05, 02:11 AM

Re: isql -- executing multiple input scripts

"Alex Vilner" <alex@sinoma.co m> wrote in message
news:22e9f6e0.0 311260935.70eab 5b8@posting.goo gle.com...[color=blue]
> 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
> :)
>[/color]

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

:r c:\scripts\scri pt1.sql
:r c:\scripts\scri pt2.sql
:r c:\scripts\scri pt3.sql

Then execute it like this:

isql -S MyServer -d MyDatabase -E -i c:\scripts\wrap per.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** · Jul 20 '05, 02:13 AM

Re: isql -- executing multiple input scripts

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" <sql@hayes.ch > wrote in message news:<3fc4f4f9_ 2@news.bluewin. ch>...[color=blue]
> "Alex Vilner" <alex@sinoma.co m> wrote in message
> news:22e9f6e0.0 311260935.70eab 5b8@posting.goo gle.com...[color=green]
> > 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
> > :)
> >[/color]
>
> Yes, if you create a wrapper file which lists each script like this:
>
> :r c:\scripts\scri pt1.sql
> :r c:\scripts\scri pt2.sql
> :r c:\scripts\scri pt3.sql
>
> Then execute it like this:
>
> isql -S MyServer -d MyDatabase -E -i c:\scripts\wrap per.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[/color]

**Erland Sommarskog** · Jul 20 '05, 02:13 AM

Re: isql -- executing multiple input scripts

Alex Vilner (alex@sinoma.co m) writes:[color=blue]
> Have an issue with this:
>
>
> 1> 80> 159> 361> 415> 447> Msg 170, Level 15, State 1, Server DEVSQL01,
> Line 4[/color]

If you use the -n options, you will not have to look at those
80> etc.
[color=blue]
> 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.
> .....[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

SQL Server 2025 redefines what's possible for enterprise data. With developer-first features and integration with analytics and AI models, SQL Server 2025 accelerates AI innovation using the data you already have.

**Simon Hayes** · Jul 20 '05, 02:13 AM

Re: isql -- executing multiple input scripts

alex@sinoma.com (Alex Vilner) wrote in message news:<22e9f6e0. 0312020634.f768 a6e@posting.goo gle.com>...[color=blue]
> 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!
>[/color]

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_IDENTIFI ER ON
GO
SET ANSI_NULLS ON
GO
if exists ...
GO
CREATE procedure dbo.SomeProc
....
GO

Simon

isql -- executing multiple input scripts

isql -- executing multiple input scripts

Comment

Comment

Comment

Comment

Comment

Comment