Hidely hodley everyone
I'd like to run a series of of sql ddl statements against an msde2000
server. Normally I just deploy cmd file that impliments as osql statement,
but I'd like to store the 'patch' in an mdb file and use say ado or even
a passthrough statement to execute it.
The problem I've got Is I access (?) seems to require me to execute one
ddl at a time, otherwise the script breaks at the 'go' statement. While I
_could_ do this - I don't really want to.
Any solutions,ideas ,tips,wraps on knuckles etc?
thanks
Glenn 7 6169
Do it as a Passthrough query. With a passthrough Access doen's interpret the
SQL it just passes it to the server to interpret and run.
--
Terry Kreft
MVP Microsoft Access
"Glenn Davy" <gS***********@ tpg.com.au> wrote in message
news:pa******** *************** *****@tpg.com.a u... Hidely hodley everyone
I'd like to run a series of of sql ddl statements against an msde2000 server. Normally I just deploy cmd file that impliments as osql statement, but I'd like to store the 'patch' in an mdb file and use say ado or even a passthrough statement to execute it.
The problem I've got Is I access (?) seems to require me to execute one ddl at a time, otherwise the script breaks at the 'go' statement. While I _could_ do this - I don't really want to.
Any solutions,ideas ,tips,wraps on knuckles etc? thanks Glenn
Hi terry
I get the same problem with a passthrough query. I get the impression with
adodb.commands access doesn't interpret either e.g.
To impliment a script that executes this sql on the server:
"ALTER PROCEDURE ThisProc() AS
SET NO COUNT OFF
EXEC dbo.someprocedu re
SELECT something from somewhere
GO
DROP PROCEDURE dbo.DontWantThi sProcanymore
GO
ALTER FUNCTION dbo.FunctionToF ix()
RETURNS INT AS
BEGIN
RETURN 10
END
GO"
I've tried both making it the body of a passthrough query, and
I've tried using something like this (please ignore obvious errors, just
pulling air code here)
sub ApplyMyPatch
dim cmd as new adodb.command
dim DDLScript as string
DDLScript = GetTheScriptCon tentFromSomewhe re()
'In this case the script is the code above
set cmd.activeconne ction=Appropria teConnection
cmd.commandtext =DDLScript
cmd.commandtype =acCmdText
cmd.execute
In either case the I get the same error & that is that it chokes on the go
statement. i.e. invalid syntax near go
Any one of these statements works fine
thx
Glenn
On Mon, 05 Jul 2004 10:13:38 +0100, Terry Kreft wrote: Do it as a Passthrough query. With a passthrough Access doen's interpret the SQL it just passes it to the server to interpret and run.
-- Terry Kreft MVP Microsoft Access
"Glenn Davy" <gS***********@ tpg.com.au> wrote in message news:pa******** *************** *****@tpg.com.a u... Hidely hodley everyone
I'd like to run a series of of sql ddl statements against an msde2000 server. Normally I just deploy cmd file that impliments as osql statement, but I'd like to store the 'patch' in an mdb file and use say ado or even a passthrough statement to execute it.
The problem I've got Is I access (?) seems to require me to execute one ddl at a time, otherwise the script breaks at the 'go' statement. While I _could_ do this - I don't really want to.
Any solutions,ideas ,tips,wraps on knuckles etc? thanks Glenn
It's SQL returning the error then.
Try it without GO and see if that works for you.
--
Terry Kreft
MVP Microsoft Access
"Glenn Davy" <gS***********@ tpg.com.au> wrote in message
news:pa******** *************** *****@tpg.com.a u... Hi terry I get the same problem with a passthrough query. I get the impression with adodb.commands access doesn't interpret either e.g.
To impliment a script that executes this sql on the server:
"ALTER PROCEDURE ThisProc() AS SET NO COUNT OFF EXEC dbo.someprocedu re SELECT something from somewhere
GO
DROP PROCEDURE dbo.DontWantThi sProcanymore GO
ALTER FUNCTION dbo.FunctionToF ix() RETURNS INT AS BEGIN RETURN 10 END GO"
I've tried both making it the body of a passthrough query, and I've tried using something like this (please ignore obvious errors, just pulling air code here)
sub ApplyMyPatch dim cmd as new adodb.command dim DDLScript as string
DDLScript = GetTheScriptCon tentFromSomewhe re() 'In this case the script is the code above
set cmd.activeconne ction=Appropria teConnection cmd.commandtext =DDLScript cmd.commandtype =acCmdText cmd.execute
In either case the I get the same error & that is that it chokes on the go statement. i.e. invalid syntax near go
Any one of these statements works fine
thx Glenn
On Mon, 05 Jul 2004 10:13:38 +0100, Terry Kreft wrote:
Do it as a Passthrough query. With a passthrough Access doen's interpret
the SQL it just passes it to the server to interpret and run.
-- Terry Kreft MVP Microsoft Access
"Glenn Davy" <gS***********@ tpg.com.au> wrote in message news:pa******** *************** *****@tpg.com.a u... Hidely hodley everyone
I'd like to run a series of of sql ddl statements against an msde2000 server. Normally I just deploy cmd file that impliments as osql
statement, but I'd like to store the 'patch' in an mdb file and use say ado or
even a passthrough statement to execute it.
The problem I've got Is I access (?) seems to require me to execute one ddl at a time, otherwise the script breaks at the 'go' statement. While
I _could_ do this - I don't really want to.
Any solutions,ideas ,tips,wraps on knuckles etc? thanks Glenn
This is the reference I was looking for, from BOL
"GO is not a Transact-SQL statement; it is a command recognized by the osql
and isql utilities and SQL Query Analyzer."
So it won't work in a Passthrough query.
--
Terry Kreft
MVP Microsoft Access
"Glenn Davy" <gS***********@ tpg.com.au> wrote in message
news:pa******** *************** *****@tpg.com.a u... Hi terry I get the same problem with a passthrough query. I get the impression with adodb.commands access doesn't interpret either e.g.
To impliment a script that executes this sql on the server:
"ALTER PROCEDURE ThisProc() AS SET NO COUNT OFF EXEC dbo.someprocedu re SELECT something from somewhere
GO
DROP PROCEDURE dbo.DontWantThi sProcanymore GO
ALTER FUNCTION dbo.FunctionToF ix() RETURNS INT AS BEGIN RETURN 10 END GO"
I've tried both making it the body of a passthrough query, and I've tried using something like this (please ignore obvious errors, just pulling air code here)
sub ApplyMyPatch dim cmd as new adodb.command dim DDLScript as string
DDLScript = GetTheScriptCon tentFromSomewhe re() 'In this case the script is the code above
set cmd.activeconne ction=Appropria teConnection cmd.commandtext =DDLScript cmd.commandtype =acCmdText cmd.execute
In either case the I get the same error & that is that it chokes on the go statement. i.e. invalid syntax near go
Any one of these statements works fine
thx Glenn
On Mon, 05 Jul 2004 10:13:38 +0100, Terry Kreft wrote:
Do it as a Passthrough query. With a passthrough Access doen's interpret
the SQL it just passes it to the server to interpret and run.
-- Terry Kreft MVP Microsoft Access
"Glenn Davy" <gS***********@ tpg.com.au> wrote in message news:pa******** *************** *****@tpg.com.a u... Hidely hodley everyone
I'd like to run a series of of sql ddl statements against an msde2000 server. Normally I just deploy cmd file that impliments as osql
statement, but I'd like to store the 'patch' in an mdb file and use say ado or
even a passthrough statement to execute it.
The problem I've got Is I access (?) seems to require me to execute one ddl at a time, otherwise the script breaks at the 'go' statement. While
I _could_ do this - I don't really want to.
Any solutions,ideas ,tips,wraps on knuckles etc? thanks Glenn
Ok - that makes sense of why it doesnt "go". However it also doesn't
function without the GO statements, breaking at a new ddl statements.
Multiple select or manipulation statments seem fine
thx
GLenn
n Mon, 05 Jul 2004 15:37:53 +0100, Terry Kreft wrote: It's SQL returning the error then.
Try it without GO and see if that works for you.
-- Terry Kreft MVP Microsoft Access
"Glenn Davy" <gS***********@ tpg.com.au> wrote in message news:pa******** *************** *****@tpg.com.a u... Hi terry I get the same problem with a passthrough query. I get the impression with adodb.commands access doesn't interpret either e.g.
To impliment a script that executes this sql on the server:
"ALTER PROCEDURE ThisProc() AS SET NO COUNT OFF EXEC dbo.someprocedu re SELECT something from somewhere
GO
DROP PROCEDURE dbo.DontWantThi sProcanymore GO
ALTER FUNCTION dbo.FunctionToF ix() RETURNS INT AS BEGIN RETURN 10 END GO"
I've tried both making it the body of a passthrough query, and I've tried using something like this (please ignore obvious errors, just pulling air code here)
sub ApplyMyPatch dim cmd as new adodb.command dim DDLScript as string
DDLScript = GetTheScriptCon tentFromSomewhe re() 'In this case the script is the code above
set cmd.activeconne ction=Appropria teConnection cmd.commandtext =DDLScript cmd.commandtype =acCmdText cmd.execute
In either case the I get the same error & that is that it chokes on the go statement. i.e. invalid syntax near go
Any one of these statements works fine
thx Glenn
On Mon, 05 Jul 2004 10:13:38 +0100, Terry Kreft wrote:
> > Do it as a Passthrough query. With a passthrough Access doen's interpret the > SQL it just passes it to the server to interpret and run. > > > -- > Terry Kreft > MVP Microsoft Access > > > "Glenn Davy" <gS***********@ tpg.com.au> wrote in message > news:pa******** *************** *****@tpg.com.a u... >> Hidely hodley everyone >> >> I'd like to run a series of of sql ddl statements against an msde2000 >> server. Normally I just deploy cmd file that impliments as osql statement, >> but I'd like to store the 'patch' in an mdb file and use say ado or even >> a passthrough statement to execute it. >> >> The problem I've got Is I access (?) seems to require me to execute one >> ddl at a time, otherwise the script breaks at the 'go' statement. While I >> _could_ do this - I don't really want to. >> >> Any solutions,ideas ,tips,wraps on knuckles etc? >> thanks >> Glenn >> >>
Have you set the "Returns Records" property to No, if it's purely DDL then
you should do this.
--
Terry Kreft
MVP Microsoft Access
"Glenn Davy" <gS***********@ tpg.com.au> wrote in message
news:pa******** *************** *****@tpg.com.a u... Ok - that makes sense of why it doesnt "go". However it also doesn't function without the GO statements, breaking at a new ddl statements. Multiple select or manipulation statments seem fine
thx GLenn
n Mon, 05 Jul 2004 15:37:53 +0100, Terry Kreft wrote:
It's SQL returning the error then.
Try it without GO and see if that works for you.
-- Terry Kreft MVP Microsoft Access
"Glenn Davy" <gS***********@ tpg.com.au> wrote in message news:pa******** *************** *****@tpg.com.a u... Hi terry I get the same problem with a passthrough query. I get the impression
with adodb.commands access doesn't interpret either e.g.
To impliment a script that executes this sql on the server:
"ALTER PROCEDURE ThisProc() AS SET NO COUNT OFF EXEC dbo.someprocedu re SELECT something from somewhere
GO
DROP PROCEDURE dbo.DontWantThi sProcanymore GO
ALTER FUNCTION dbo.FunctionToF ix() RETURNS INT AS BEGIN RETURN 10 END GO"
I've tried both making it the body of a passthrough query, and I've tried using something like this (please ignore obvious errors,
just pulling air code here)
sub ApplyMyPatch dim cmd as new adodb.command dim DDLScript as string
DDLScript = GetTheScriptCon tentFromSomewhe re() 'In this case the script is the code above
set cmd.activeconne ction=Appropria teConnection cmd.commandtext =DDLScript cmd.commandtype =acCmdText cmd.execute
In either case the I get the same error & that is that it chokes on the
go statement. i.e. invalid syntax near go
Any one of these statements works fine
thx Glenn
On Mon, 05 Jul 2004 10:13:38 +0100, Terry Kreft wrote:
> > Do it as a Passthrough query. With a passthrough Access doen's
interpret the > SQL it just passes it to the server to interpret and run. > > > -- > Terry Kreft > MVP Microsoft Access > > > "Glenn Davy" <gS***********@ tpg.com.au> wrote in message > news:pa******** *************** *****@tpg.com.a u... >> Hidely hodley everyone >> >> I'd like to run a series of of sql ddl statements against an
msde2000 >> server. Normally I just deploy cmd file that impliments as osql statement, >> but I'd like to store the 'patch' in an mdb file and use say ado or even >> a passthrough statement to execute it. >> >> The problem I've got Is I access (?) seems to require me to execute
one >> ddl at a time, otherwise the script breaks at the 'go' statement.
While I >> _could_ do this - I don't really want to. >> >> Any solutions,ideas ,tips,wraps on knuckles etc? >> thanks >> Glenn >> >>
Hi Terry - Thanks for all your help so far. I hadn't done this but, I have
now, but I still get the same error. bumma
thanks again
Glenn Have you set the "Returns Records" property to No, if it's purely DDL then you should do this.
-- Terry Kreft MVP Microsoft Access
"Glenn Davy" <gS***********@ tpg.com.au> wrote in message news:pa******** *************** *****@tpg.com.a u... Ok - that makes sense of why it doesnt "go". However it also doesn't function without the GO statements, breaking at a new ddl statements. Multiple select or manipulation statments seem fine
thx GLenn
n Mon, 05 Jul 2004 15:37:53 +0100, Terry Kreft wrote:
> It's SQL returning the error then. > > Try it without GO and see if that works for you. > > > -- > Terry Kreft > MVP Microsoft Access > > > "Glenn Davy" <gS***********@ tpg.com.au> wrote in message > news:pa******** *************** *****@tpg.com.a u... >> Hi terry >> I get the same problem with a passthrough query. I get the impression with >> adodb.commands access doesn't interpret either e.g. >> >> To impliment a script that executes this sql on the server: >> >> "ALTER PROCEDURE ThisProc() AS >> SET NO COUNT OFF >> EXEC dbo.someprocedu re >> SELECT something from somewhere >> >> GO >> >> DROP PROCEDURE dbo.DontWantThi sProcanymore >> GO >> >> ALTER FUNCTION dbo.FunctionToF ix() >> RETURNS INT AS >> BEGIN >> RETURN 10 >> END >> GO" >> >> I've tried both making it the body of a passthrough query, and >> I've tried using something like this (please ignore obvious errors, just >> pulling air code here) >> >> sub ApplyMyPatch >> dim cmd as new adodb.command >> dim DDLScript as string >> >> DDLScript = GetTheScriptCon tentFromSomewhe re() >> 'In this case the script is the code above >> >> set cmd.activeconne ction=Appropria teConnection >> cmd.commandtext =DDLScript >> cmd.commandtype =acCmdText >> cmd.execute >> >> In either case the I get the same error & that is that it chokes on the go >> statement. i.e. invalid syntax near go >> >> Any one of these statements works fine >> >> thx >> Glenn >> >> >> >> >> >> On Mon, 05 Jul 2004 10:13:38 +0100, Terry Kreft wrote: >> >> > >> > Do it as a Passthrough query. With a passthrough Access doen's interpret > the >> > SQL it just passes it to the server to interpret and run. >> > >> > >> > -- >> > Terry Kreft >> > MVP Microsoft Access >> > >> > >> > "Glenn Davy" <gS***********@ tpg.com.au> wrote in message >> > news:pa******** *************** *****@tpg.com.a u... >> >> Hidely hodley everyone >> >> >> >> I'd like to run a series of of sql ddl statements against an msde2000 >> >> server. Normally I just deploy cmd file that impliments as osql > statement, >> >> but I'd like to store the 'patch' in an mdb file and use say ado or > even >> >> a passthrough statement to execute it. >> >> >> >> The problem I've got Is I access (?) seems to require me to execute one >> >> ddl at a time, otherwise the script breaks at the 'go' statement. While > I >> >> _could_ do this - I don't really want to. >> >> >> >> Any solutions,ideas ,tips,wraps on knuckles etc? >> >> thanks >> >> Glenn >> >> >> >> >>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Shmuel |
last post by:
Is it possible to query multiple statements at once?
Like:
$query = "set @p := 1; select @p + 1";
$results = mysql_query($query);
I'm thinking of PHP4. There is in mysqli the prepare statement,
but I can't use that.
Shmuel.
|
by: Tim Hastings |
last post by:
Hello,
I am using MyODBC from VB and I want to submit a batch of insert statements
in one call from my App. This is more efficient than making multiple calls
from code because of the communication overhead.
If I send a batch multiple statements separated by ; or ; + newline I get
syntax errors pointing at the start of the next statement..
If I fire each statement one at a time with the ; at the end there's no
problem.
|
by: Dean g |
last post by:
Hi, I have a problem with running multiple sql statements using asp.
Basically if there is an error with any of the statements inside the
query a rollback is done. the sql and rollback work fine, But on the
actual asp page no error is detected unless it occurs in the first
statement in the query. heres an example
<%
Sql= " BEGIN TRAN INSERT INTO Users VALUES ('BLAH', 'BLAH') INSERT INTO
TESTING VALUES ('SOMETHING','SOMETHING') IF @@error...
|
by: Erik Haugen |
last post by:
This item in the C++ faq:
http://www.parashift.com/c++-faq-lite/misc-technical-issues.html#faq-38.5
discusses macros with multiple statements.
The problem is that a macro such as
#define MYMACRO(a,b) stmt1; stmt2;
will cause unwanted astonishment if you say:
|
by: DG |
last post by:
Hi,
Can anyone advise how to execute multiple statements in a single query
batch. For example-
update customers set customer_name = 'Smith' where customer_name =
'Smyth';
select * from customers;
I can execute each statement individually but get the 'you have an error in
| |
by: Annie D via AccessMonster.com |
last post by:
Hi,
Is it possible to use multiple statements in SQL?? (I’ve never used it before)
:
I have one query that i'm working with, The statements I want to use are as
below, they all work individually, but not together, Access help is as much
use as a chocolate fireguard in this case! any suggestions or explainations
would be gratefully appreciated:
|
by: rudykayna |
last post by:
I'm having trouble executing multiple DDL statements in one SQL file. I've been using ExecuteNonQuery() but it does not seem to like the "GO" statements in my SQL file. I need to keep the "GO" statements because its DDL. Anyone know a way around this?
---
Posted using Wimdows.net NntpNews Component - Posted from .NET's Largest Community Website: http://www.dotnetjunkies.com/newsgroups/
|
by: arthy |
last post by:
Hi,
Is it possible to execute multiple statements on to the database using a single dbconnection object.what is the drawback in using .If not possible ,then how can the execution of multiple statements using a single object be done.
thanks in advance,
arthy
|
by: harsha318 |
last post by:
Hi
I need to have a single query and which can have multiple statements
For eg:
string str = string.Empty;
str = "select * from Customers;Select * from Orders";
iDB2Connection iDB2con = new iDB2Connection(connStr);
iDB2Command iDBCmd = iDB2con.CreateCommand();
|
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: 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: 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...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |