Re: Executing Stored Proc from file in Ado .NET

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: CliffNews (dainea_at_hotmail.com)
Date: 04/10/04 

Date: Sat, 10 Apr 2004 17:21:21 -0400

I forgot to mention one other thing, I actually replace 'GO' and tried to
run the script in Query Analyzer and got the same error as well.

"Joe Fallon" <jfallon1@nospamtwcny.rr.com> wrote in message
news:OI%23riazHEHA.1608@TK2MSFTNGP11.phx.gbl...
> I think the suggestion was to replace GO with ; everywhere in the file.
> This will send multiple SQL statements to SQL Server which has the ability
> to run them consecutively.
> So it should work.
> Some other DBs don't have this ability.
> --
> Joe Fallon
>
>
>
>
> "CliffNews" <dainea@hotmail.com> wrote in message
> news:LxWdc.10061$Ix2.591@fe30.usenetserver.com...
> > Thanks for the quick reply Val. I'm not sure if I quite follow you. Are
> you
> > saying for each statement in my batch I should terminate it with a (;).
> e.g
> >
> > if exists (select * from dbo.sysobjects
> > where id = object_id(N'[dbo].[delete_datafeed_response]')
> > and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> > drop procedure [dbo].[delete_datafeed_response];
> >
> >
> > create procedure [dbo].[delete_datafeed_response] @day_delete_interval
int
> > as
> > begin
> > set nocount on
> > declare @error int, @message varchar(200), @rows_deleted int;
> > select @error = 0;
> >
> > if @day_delete_interval > 0
> > begin
> > DELETE FROM [delete_datafeed_response];
> > WHERE [status_code] = 'CLOSED';
> > AND (DATEDIFF(d, [modify_date], getdate()) >
> @day_delete_interval);
> >
> > end;
> >
> > I'm not sure if this is what you mean (that is putting semi-colon at the
> end
> > of each statement). If so that will be a pain considering the procs that
> i'm
> > executing can be rather huge in upwards of 4 megs. Streaming through
these
> > files and putting (;) at the end of each line will be pain.
> >
> > "Val Mazur" <group51a@hotmail.com> wrote in message
> > news:uZOxbEyHEHA.3356@TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > Since GO is not a valid SQL statement, but batch statement, you cannot
> use
> > > it in your batch. What you could do in this case is to separate your
> > > statements inside of the batch, suing semicolon (;). In this case
OLEDB
> > > provider for SQL Server will recognize each statement properly and
will
> > > execute them separately. But it works with SQL Server provider. Some
> other
> > > providers do not support this feature
> > >
> > > --
> > > Val Mazur
> > > Microsoft MVP
> > >
> > >
> > > "CliffNews" <dainea@hotmail.com> wrote in message
> > > news:R7Vdc.45006$W6.43639@fe08.usenetserver.com...
> > > > Hi all,
> > > > I'm trying to a automate the execution of a stored procedure from a
> > file.
> > > > The problem is when I remove the 'GO' command, I get "'CREATE
> PROCEDURE'
> > > > must be the first statement in a query batch." Is there a way around
> > > > this??
> > > > I would hate to have to parse my "if exist" statement and run that
> > > > separately. And I really don't want to shell out and use sol server
> > > > command
> > > > line tool.
> > > >
> > > > code snippet:
> > > > if exists (select * from dbo.sysobjects
> > > > where id = object_id(N'[dbo].[delete_datafeed_response]')
> > > > and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> > > > drop procedure [dbo].[delete_datafeed_response]
> > > >
> > > > GO
> > > > create procedure [dbo].[delete_datafeed_response]
@day_delete_interval
> > int
> > > > as
> > > > begin
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >
>
>

Relevant Pages

  • Re: Feats in 4e
    ... Spellcasting could be Int for book knowledge, Wis for divine ... that had different types of magic driven by different ability scores. ... spells known, Wis capped maximum spell level and bonus slots, Charisma ...
    (rec.games.frp.dnd)
  • Re: Adding the ability to add functions into structures?
    ... Wouldn't it be useful to also have the ability to collect variable and functions? ... int; ... // Sets this collection read-only or unsets the read-only flag ... ArrayListInterface *lpVtbl; ...
    (comp.lang.c)
  • Re: An example of unions not being type safe?
    ... also say that an int is not typesafe because ... What makes it possible to break the type system is the existence ... of multiple types and the ability to convert between them. ... doesn't break type safety. ...
    (comp.lang.c)
  • blink driver power saving
    ... Using a notifier gives same effect. ... Also add ability to unload module. ... -static int blink_init ...
    (Linux-Kernel)