473,699 Members | 2,150 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

isql -- executing multiple input scripts

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
Jul 20 '05 #1
6 14549
al**@sinoma.com (Alex Vilner) wrote in message news:<22******* *************** ****@posting.go ogle.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
Jul 20 '05 #2
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.go ogle.com>...
al**@sinoma.com (Alex Vilner) wrote in message news:<22******* *************** ****@posting.go ogle.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

Jul 20 '05 #3

"Alex Vilner" <al**@sinoma.co m> wrote in message
news:22******** *************** ***@posting.goo gle.com...
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\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
Jul 20 '05 #4
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.co m> wrote in message
news:22******** *************** ***@posting.goo gle.com...
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\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

Jul 20 '05 #5
Alex Vilner (al**@sinoma.co m) 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
Jul 20 '05 #6
al**@sinoma.com (Alex Vilner) wrote in message news:<22******* *************** ***@posting.goo gle.com>...
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_IDENTIFI ER ON
GO
SET ANSI_NULLS ON
GO
if exists ...
GO
CREATE procedure dbo.SomeProc
....
GO
Simon
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1956
by: Nick Coghlan | last post by:
Anyone playing with the CPython interpreter's new command line switch might have noticed that it only works with top-level modules (i.e. scripts that are directly on sys.path). If the script is inside a package, the invocation will fail with a "Module not found" error. This PEP is aimed at fixing that :) Cheers, Nick.
15
2603
by: Nick Coghlan | last post by:
Python 2.4's -m command line switch only works for modules directly on sys.path. Trying to use it with modules inside packages will fail with a "Module not found" error. This PEP aims to fix that for Python 2.5. Previously, posting of a draft version of the PEP to python-dev and python-list didn't actually generate any responses. I'm not sure if that's an indication that people don't see the restriction to top-level modules as a problem...
1
5151
by: Jacob Grydholt Jensen | last post by:
I am trying to learn iSQL*Plus for the 1Z0-007 exam. I am having a bit of problems with the substitution variables. My understanding was that they behave as in SQL*Plus, but my experiments beg to differ. I log on to the sample schema 'hr' and enter the following statements: -- statements begin set verify on define name = K%
2
12742
by: RJ | last post by:
We currently send product releases to our customers, and often have to include scripts that need to be ran on the Oracle databases (also do it for SqlServer customers, but we use a different set of scripts for that). Some of the Sql scripts can be quite long, and so we break them out to their own script files and call them all from one main ..sql file, using sqlplus to execute it all. The admin would run the file like:
4
12679
by: chris.dunigan | last post by:
I'm looking for an example of how to execute an existing DTS­ package from an ASP (VB)script and would appreciate any and all response. ­I don't even know if it's possible Thanks - Chuck Gatto Dan Guzman Apr 27 2000, 12:00 am show options
4
3821
by: Marcin Zmyslowski | last post by:
Hello everyone! I have the following problem. I`ve opened a Query Analyser and write the statement: -isql -S 'PL6XXXX' -i 'd:\SQL_Server_2000\Raport_WWW.sql' I got the following erorr message: Incorrect syntax near -S
3
8593
by: yanakal | last post by:
Hi, I'm using isql to query data and output the same to a flat file. The isql has the following command options ' -h-1 -w500 -n -b -s"" '. In the SQL_CODE, the first two lines before the select statement are use dbname set nocount on go When I run this, an additional blank line is put into the output file. Actually, there are two lines after the last result set in the output file. This file is being fed into another system and the...
1
11268
by: Philip Bondi | last post by:
Hello to all SQL Server junkies who work with non-English characters: For people running scripts from the command line using ANSI files with special characters, it is very important to use isql and disable "Automatic ANSI to OEM conversion": - This only affects isql from the command line, and no gui applications - http://support.microsoft.com/?scid=kb;EN-US;153449 - Start the "Client Network Utility" C:\WINDOWS\system32\cliconfg.exe
3
14740
by: axtens | last post by:
G'day everyone That's a space between the ticks. It's all part of a longer script but seeing as the failure occurs on line 1 if exists (select * from dbo.sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
0
8622
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8930
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8892
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7761
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6538
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4381
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4634
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3061
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2358
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.