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
6  14554  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
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
"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
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
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 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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.
|
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...
|
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%
|
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:
|
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
| | |
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
|
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...
|
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
|
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)
|
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,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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,...
|
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...
|
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...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
