Executing multiple sql DDL statements

[Glenn Davy]

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

[Terry Kreft]

Re: Executing multiple sql DDL statements


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" <gSPAMLESSdavy@ tpg.com.au> wrote in message
news:pan.2004.0 7.05.01.39.24.5 65902@tpg.com.a u...[color=blue]
> 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
>
>[/color]

[Glenn Davy]

Re: Executing multiple sql DDL statements

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:
[color=blue]
>
> 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" <gSPAMLESSdavy@ tpg.com.au> wrote in message
> news:pan.2004.0 7.05.01.39.24.5 65902@tpg.com.a u...[color=green]
>> 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
>>
>>[/color][/color]

[Terry Kreft]

Re: Executing multiple sql DDL statements

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" <gSPAMLESSdavy@ tpg.com.au> wrote in message
news:pan.2004.0 7.05.14.25.57.8 05024@tpg.com.a u...[color=blue]
> 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:
>[color=green]
> >
> > Do it as a Passthrough query. With a passthrough Access doen's interpret[/color][/color]
the[color=blue][color=green]
> > SQL it just passes it to the server to interpret and run.
> >
> >
> > --
> > Terry Kreft
> > MVP Microsoft Access
> >
> >
> > "Glenn Davy" <gSPAMLESSdavy@ tpg.com.au> wrote in message
> > news:pan.2004.0 7.05.01.39.24.5 65902@tpg.com.a u...[color=darkred]
> >> 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[/color][/color][/color]
statement,[color=blue][color=green][color=darkred]
> >> but I'd like to store the 'patch' in an mdb file and use say ado or[/color][/color][/color]
even[color=blue][color=green][color=darkred]
> >> 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[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> >> _could_ do this - I don't really want to.
> >>
> >> Any solutions,ideas ,tips,wraps on knuckles etc?
> >> thanks
> >> Glenn
> >>
> >>[/color][/color]
>[/color]

[Terry Kreft]

Re: Executing multiple sql DDL statements

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" <gSPAMLESSdavy@ tpg.com.au> wrote in message
news:pan.2004.0 7.05.14.25.57.8 05024@tpg.com.a u...[color=blue]
> 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:
>[color=green]
> >
> > Do it as a Passthrough query. With a passthrough Access doen's interpret[/color][/color]
the[color=blue][color=green]
> > SQL it just passes it to the server to interpret and run.
> >
> >
> > --
> > Terry Kreft
> > MVP Microsoft Access
> >
> >
> > "Glenn Davy" <gSPAMLESSdavy@ tpg.com.au> wrote in message
> > news:pan.2004.0 7.05.01.39.24.5 65902@tpg.com.a u...[color=darkred]
> >> 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[/color][/color][/color]
statement,[color=blue][color=green][color=darkred]
> >> but I'd like to store the 'patch' in an mdb file and use say ado or[/color][/color][/color]
even[color=blue][color=green][color=darkred]
> >> 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[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> >> _could_ do this - I don't really want to.
> >>
> >> Any solutions,ideas ,tips,wraps on knuckles etc?
> >> thanks
> >> Glenn
> >>
> >>[/color][/color]
>[/color]

[Glenn Davy]

Re: Executing multiple sql DDL statements


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:
[color=blue]
> 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" <gSPAMLESSdavy@ tpg.com.au> wrote in message
> news:pan.2004.0 7.05.14.25.57.8 05024@tpg.com.a u...[color=green]
>> 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:
>>[color=darkred]
>> >
>> > Do it as a Passthrough query. With a passthrough Access doen's interpret[/color][/color]
> the[color=green][color=darkred]
>> > SQL it just passes it to the server to interpret and run.
>> >
>> >
>> > --
>> > Terry Kreft
>> > MVP Microsoft Access
>> >
>> >
>> > "Glenn Davy" <gSPAMLESSdavy@ tpg.com.au> wrote in message
>> > news:pan.2004.0 7.05.01.39.24.5 65902@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[/color][/color]
> statement,[color=green][color=darkred]
>> >> but I'd like to store the 'patch' in an mdb file and use say ado or[/color][/color]
> even[color=green][color=darkred]
>> >> 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[/color][/color]
> I[color=green][color=darkred]
>> >> _could_ do this - I don't really want to.
>> >>
>> >> Any solutions,ideas ,tips,wraps on knuckles etc?
>> >> thanks
>> >> Glenn
>> >>
>> >>[/color]
>>[/color][/color]

[Terry Kreft]

Re: Executing multiple sql DDL statements

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" <gSPAMLESSdavy@ tpg.com.au> wrote in message
news:pan.2004.0 7.06.00.47.31.1 12649@tpg.com.a u...[color=blue]
>
> 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:
>[color=green]
> > 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" <gSPAMLESSdavy@ tpg.com.au> wrote in message
> > news:pan.2004.0 7.05.14.25.57.8 05024@tpg.com.a u...[color=darkred]
> >> Hi terry
> >> I get the same problem with a passthrough query. I get the impression[/color][/color][/color]
with[color=blue][color=green][color=darkred]
> >> 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,[/color][/color][/color]
just[color=blue][color=green][color=darkred]
> >> 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[/color][/color][/color]
go[color=blue][color=green][color=darkred]
> >> 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[/color][/color][/color]
interpret[color=blue][color=green]
> > the[color=darkred]
> >> > SQL it just passes it to the server to interpret and run.
> >> >
> >> >
> >> > --
> >> > Terry Kreft
> >> > MVP Microsoft Access
> >> >
> >> >
> >> > "Glenn Davy" <gSPAMLESSdavy@ tpg.com.au> wrote in message
> >> > news:pan.2004.0 7.05.01.39.24.5 65902@tpg.com.a u...
> >> >> Hidely hodley everyone
> >> >>
> >> >> I'd like to run a series of of sql ddl statements against an[/color][/color][/color]
msde2000[color=blue][color=green][color=darkred]
> >> >> server. Normally I just deploy cmd file that impliments as osql[/color]
> > statement,[color=darkred]
> >> >> but I'd like to store the 'patch' in an mdb file and use say ado or[/color]
> > even[color=darkred]
> >> >> a passthrough statement to execute it.
> >> >>
> >> >> The problem I've got Is I access (?) seems to require me to execute[/color][/color][/color]
one[color=blue][color=green][color=darkred]
> >> >> ddl at a time, otherwise the script breaks at the 'go' statement.[/color][/color][/color]
While[color=blue][color=green]
> > I[color=darkred]
> >> >> _could_ do this - I don't really want to.
> >> >>
> >> >> Any solutions,ideas ,tips,wraps on knuckles etc?
> >> >> thanks
> >> >> Glenn
> >> >>
> >> >>
> >>[/color][/color]
>[/color]

[Glenn Davy]

Re: Executing multiple sql DDL statements

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

[color=blue]
> 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" <gSPAMLESSdavy@ tpg.com.au> wrote in message
> news:pan.2004.0 7.06.00.47.31.1 12649@tpg.com.a u...[color=green]
>>
>> 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:
>>[color=darkred]
>> > 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" <gSPAMLESSdavy@ tpg.com.au> wrote in message
>> > news:pan.2004.0 7.05.14.25.57.8 05024@tpg.com.a u...
>> >> Hi terry
>> >> I get the same problem with a passthrough query. I get the impression[/color][/color]
> with[color=green][color=darkred]
>> >> 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,[/color][/color]
> just[color=green][color=darkred]
>> >> 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[/color][/color]
> go[color=green][color=darkred]
>> >> 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[/color][/color]
> interpret[color=green][color=darkred]
>> > the
>> >> > SQL it just passes it to the server to interpret and run.
>> >> >
>> >> >
>> >> > --
>> >> > Terry Kreft
>> >> > MVP Microsoft Access
>> >> >
>> >> >
>> >> > "Glenn Davy" <gSPAMLESSdavy@ tpg.com.au> wrote in message
>> >> > news:pan.2004.0 7.05.01.39.24.5 65902@tpg.com.a u...
>> >> >> Hidely hodley everyone
>> >> >>
>> >> >> I'd like to run a series of of sql ddl statements against an[/color][/color]
> msde2000[color=green][color=darkred]
>> >> >> 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[/color][/color]
> one[color=green][color=darkred]
>> >> >> ddl at a time, otherwise the script breaks at the 'go' statement.[/color][/color]
> While[color=green][color=darkred]
>> > I
>> >> >> _could_ do this - I don't really want to.
>> >> >>
>> >> >> Any solutions,ideas ,tips,wraps on knuckles etc?
>> >> >> thanks
>> >> >> Glenn
>> >> >>
>> >> >>
>> >>[/color]
>>[/color][/color]