473,326 Members | 2,127 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Executing multiple sql DDL statements

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
Nov 13 '05 #1
7 6138

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.au...
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

Nov 13 '05 #2
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.someprocedure
SELECT something from somewhere

GO

DROP PROCEDURE dbo.DontWantThisProcanymore
GO

ALTER FUNCTION dbo.FunctionToFix()
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 = GetTheScriptContentFromSomewhere()
'In this case the script is the code above

set cmd.activeconnection=AppropriateConnection
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.au...
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


Nov 13 '05 #3
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.au...
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.someprocedure
SELECT something from somewhere

GO

DROP PROCEDURE dbo.DontWantThisProcanymore
GO

ALTER FUNCTION dbo.FunctionToFix()
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 = GetTheScriptContentFromSomewhere()
'In this case the script is the code above

set cmd.activeconnection=AppropriateConnection
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.au...
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

Nov 13 '05 #4
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.au...
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.someprocedure
SELECT something from somewhere

GO

DROP PROCEDURE dbo.DontWantThisProcanymore
GO

ALTER FUNCTION dbo.FunctionToFix()
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 = GetTheScriptContentFromSomewhere()
'In this case the script is the code above

set cmd.activeconnection=AppropriateConnection
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.au...
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

Nov 13 '05 #5

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.au...
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.someprocedure
SELECT something from somewhere

GO

DROP PROCEDURE dbo.DontWantThisProcanymore
GO

ALTER FUNCTION dbo.FunctionToFix()
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 = GetTheScriptContentFromSomewhere()
'In this case the script is the code above

set cmd.activeconnection=AppropriateConnection
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.au...
>> 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
>>
>>


Nov 13 '05 #6
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.au...

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.au...
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.someprocedure
SELECT something from somewhere

GO

DROP PROCEDURE dbo.DontWantThisProcanymore
GO

ALTER FUNCTION dbo.FunctionToFix()
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 = GetTheScriptContentFromSomewhere()
'In this case the script is the code above

set cmd.activeconnection=AppropriateConnection
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.au...
>> 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
>>
>>

Nov 13 '05 #7
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.au...

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.au...
>> 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.someprocedure
>> SELECT something from somewhere
>>
>> GO
>>
>> DROP PROCEDURE dbo.DontWantThisProcanymore
>> GO
>>
>> ALTER FUNCTION dbo.FunctionToFix()
>> 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 = GetTheScriptContentFromSomewhere()
>> 'In this case the script is the code above
>>
>> set cmd.activeconnection=AppropriateConnection
>> 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.au...
>> >> 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
>> >>
>> >>
>>


Nov 13 '05 #8

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

Similar topics

2
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...
0
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...
3
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,...
1
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...
4
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...
2
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...
1
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"...
1
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...
0
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 =...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.